?
Solved

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

Posted on 2008-10-03
3
Medium Priority
?
216 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:Brendt Hess
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:
Brendt Hess earned 500 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 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