Solved

how to get stored procedure with "exists" to return a recordset my code can use

Posted on 2008-10-03
3
199 Views
Last Modified: 2010-04-21
Now that I've got "exists" working, when I put it into my stored procedure, I'm not sure how to work with it from my code. My code is:
Set rst = cnn.Execute("AppealExistsSP '" & ParcelNo & "', " & TaxYear)

It seems that whether or not a row exists, the recordset "rst" is always closed. So how can I tell if the 'true' from the stored procedure was printed or not, so my code can decide what to do?
ALTER PROCEDURE [dbo].[AppealExistsSP] 

	@strParNo as varchar(35),

	@intTaxYr as smallint

AS

if exists (select * from Appeals where ParcelNo=@strParNo and TaxYear=@intTaxYr)

begin

print 'true'

end

Open in new window

0
Comment
Question by:GordonPrince
  • 2
3 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 22635040
Don't print 'true', SELECT it:


ALTER PROCEDURE [dbo].[AppealExistsSP] 

        @strParNo as varchar(35),

        @intTaxYr as smallint

AS

if exists (select * from Appeals where ParcelNo=@strParNo and TaxYear=@intTaxYr)

begin

    SELECT 'true' as Result

END

ELSE

    SELECT 'false' as Result

end

Open in new window

0
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
ID: 22635054
Alternate syntax, using a single SQL Statement:
ALTER PROCEDURE [dbo].[AppealExistsSP] 

        @strParNo as varchar(35),

        @intTaxYr as smallint

AS

SELECT CASE

   WHEN exists (select * from Appeals where ParcelNo=@strParNo and TaxYear=@intTaxYr)

      THEN 'true'

   ELSE 'false'

END as Result

Open in new window

0
 
LVL 4

Author Closing Comment

by:GordonPrince
ID: 31502802
This is very helpful. Thanks for your prompt responses.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now