Solved

Stored Pocedure return value

Posted on 2011-03-07
4
358 Views
Last Modified: 2012-05-11
In the following SP, how can I return1 for the update and delete and 0 if no update and delete?

IF NOT EXISTS (
     SELECT a.* FROM Table1 where qty > 100 and Id=@Id
         
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id
       delete from Table1 where Id=@Id
   END

thanks

ayha
0
Comment
Question by:ayha1999
4 Comments
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 50 total points
Comment Utility
Like this -

IF NOT EXISTS (
     SELECT a.* FROM Table1 where qty > 100 and Id=@Id
         
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id
       delete from Table1 where Id=@Id
       RETURN 1
   END
ELSE
 BEGIN
     RETURN 0
 END
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
You would be better to SELECT 1 / SELECT 0 rather than RETURN purely because you can use ExecuteScalar() to retrieve the value if you use SELECT, otherwise you have to mess about with return parameters on your command object.
0
 
LVL 2

Accepted Solution

by:
Michael-Thomas earned 75 total points
Comment Utility
You could also use an output parameter to return values.  

create procedure dbo.MyStoredProcedure @ID INT, @Field1 varchar(10), @Updated BIT OUTPUT
as
IF NOT EXISTS (SELECT a.* FROM Table1 where qty > 100 and Id=@Id)
          
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id
       delete from Table1 where Id=@Id
		set @Updated = 1
   END 
 ELSE
	SET @Updated = 0

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

Your statement does not seem to be correct

  IF NOT EXISTS (SELECT a.* FROM Table1 where qty > 100 and Id=@Id) --this means the record is not found  
  BEGIN
       UPDATE Table1 SET field1=@field1 where Id=@Id  //what exactly are you updating, the search condition was not satisfied
       delete from Table1 where Id=@Id                          //will delete all records with id=@id  ????
   END
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

6 Experts available now in Live!

Get 1:1 Help Now