IF Exists stored procedure
-
I am using an If Exists statement to see if there is any data in the table. If there is it returns 1 and if not it returns 0. I am doing this so I can display an error message if the search was unsuccessful otherwise I need the data from the table to be returned so I can capture it in a dataset.
This is the code I have so far in my stored procedure:
IF EXISTS(SELECT User_ID FROM dbo.User_Information
WHERE First_Name = @FirstName AND Last_Name = @LastName AND Email_1 = @Email)
RETURN 1
How do I get it to return the information from the table?
-
IF EXISTS(SELECT User_ID FROM dbo.User_Information
WHERE First_Name = @FirstName AND Last_Name = @LastName AND Email_1 = @Email)
begin
Select col1, col2... from YourTable
return 1
end
else
begin
return 0
end
-
to return your data as a rowset, try...
declare @rc int, @error int
SELECT User_ID FROM dbo.User_Information
WHERE First_Name = @FirstName AND Last_Name = @LastName AND Email_1 = @Email
set @rc = @@rowcount, @error = @@error
if @rc = 1 and @error = 0
return 0 -- success!
else
return 1 -- failure
that one will always attempt to return information as a recordset, but also let you know if it failed with the return code.
this one returns data as output parameters.
it will assign @user_id to 1 (hopefully there can only be one) of the userid's returned by the select statement as an output parameter. to return more parameters this way, just put something like SELECT @user_id = User_ID, @First_Name = FirstName from...
create proc usp_p2
(/*add your output parameters here...*/ @user_id int output)
as
SELECT @user_id = User_ID FROM dbo.User_Information
WHERE First_Name = @FirstName AND Last_Name = @LastName AND Email_1 = @Email
set @rc = @@rowcount, @error = @@error
if @rc = 1 and @error = 0
return 0 -- success!
else
return 1 -- failure
-Ray Metz
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply