Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Pocedure return value

Posted on 2011-03-07
4
Medium Priority
?
379 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
[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
4 Comments
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 200 total points
ID: 35059346
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
ID: 35059362
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 300 total points
ID: 35063259
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:Ephraim Wangoya
ID: 35063322

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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