Solved

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

Posted on 2008-10-03
3
200 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

861 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

23 Experts available now in Live!

Get 1:1 Help Now