SQL Server Performance Forum – Threads Archive

executing a stored procedure by passing parameters

hello! I have been trying to execute the following stored procedure
I have successfully created the procedure.
but while executing it in querry analyzer window i am getting the following error:
Syntax error converting the varchar value ‘new’ to a column of data type int. i want to get the anser in my aspx page in a label that, new which is returned by @res
but there is an error in the stored procedure itself please any body solv this problem i have created the stored procedure and executed in the following way. stored procedure:
create procedure pro_to_cheq_logindetails
(@userid varchar(30),
@pwd varchar(30),
@res varchar(3)output)
as
declare @counter int
declare @actualid varchar(30)
declare @actualpwd varchar(30)
select @counter=count(*) from ADMIN
if(@counter<>0)
begin
select @actualid=userid from ADMIN
if(@[email protected])
begin
select @actualpwd=pwd from ADMIN
if(@[email protected])
begin
select @res=’ADM’
end
end
else
begin
select @counter=count(*) from LOGIN where [email protected]
if(@counter=1)
begin
select @actualpwd=pwd from LOGIN where [email protected]
if(@[email protected])
begin
select @res=’ord’
end
else
begin
select @res=’no’
end
end
end end
else
begin
select @res=’new’
end
return @res
go
its execution:
exec pro_to_cheq_logindetails ‘dnelson’, ‘dean2003’ KK

---

you can only use an integer column to use with RETURN.
alter "return @res" to "return" and it will work. If you execute @rc=yourproc then @rc will contain the int-value provided with "RETURN "

---

Hi,
Remove RETURN statement from your SP and rest keep as it is. To execute the SP, try this way:
declare @res varchar(3)
exec pro_to_cheq_logindetails ‘dnelson’, ‘dean2003’,@res OUTPUT
select @res

---

From Books Online (for 2000, should be the same for 2005):

Is the client app expecting a resultset, or reading parameters from an object? (1) Drop the RETURN command from the procedure. (2) When calling the procedure, you need to add a variable to ‘receive’ the return value: declare @test VARCHAR(3) exec dbo.pro_to_cheq_logindetails ‘dnelson’, ‘dean2003’, @test OUTPUT After this point, the @test variable contains the result. *** By the way, you should try to avoid using so many variables. You can get your answers in a more direct way: CREATE PROCEDURE dbo.pro_to_cheq_logindetails (@userid varchar(30), @pwd varchar(30), @res varchar(3)output)
as IF EXISTS (SELECT userID FROM ADMIN WHERE userid = @userid AND pwd = @pwd)
BEGIN
SET @res = ‘ADM’
END
ELSE
IF EXISTS (SELECT userID FROM LOGIN WHERE [email protected] AND pwd = @pwd)
BEGIN
SET @res=’ord’
END
ELSE
IF EXISTS (SELECT userID FROM LOGIN WHERE [email protected])
BEGIN
SELECT @res=’no’
END SET @res = ISNULL(@res, ‘new’)

---

]]>