Solved

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

Posted on 2008-10-03
3
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 41
Are triggers slow? 7 25
How to create XML in below format from SQL Query in SQL Server? 3 47
Syntax issue with my Where Clause SQL 2012 20 38
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.…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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