ayha1999
asked on
Stored Pocedure return value
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Carl Tawn
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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