error in stored procedure output in sql server 2005

Posted by reddysankark-13471 under Sql Server on 4/29/2012 | Points: 10 | Views : 1404 | Status : [Member] | Replies : 7
hi

Create PROCEDURE CheckUserName
(

@name nvarchar(50), --Input parameter , Studentid of the student
@return int OUT -- Out parameter declared with the help of OUT keyword
)
AS
begin
declare @pid int;
set @pid=(select name from login where name=@name);
if(@pid=1)
begin
set @return=1
end
else
set @return=0
end
execute CheckUserName 'sankar'

while executing this its showing error like this

Msg 201, Level 16, State 4, Procedure CheckUserName, Line 0
Procedure or function 'CheckUserName' expects parameter '@return', which was not supplied.

sankarreddy


Responses

Posted by: Patil_Rakesh on: 4/29/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
(select name from login where name=@name)
this query must return int datatype...
and remove '(' from if condition

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

I agree with Patil_Rakesh

and, The main reason for the Error is
- You have used two Parameters("@name" as an IN Parameter and "@return" as an OUT parameters)
So, when you call the procedure, You have missed-out the OUT parameter. It has to be like as follows...
DECLARE @Ret Int

EXEC CheckUserName 'Sankar', @Ret OUTPUT
SELECT @Ret


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patil_Rakesh on: 4/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
Pandians sir,

if sankar reddy is going to search the record, which exist in table or not,
then this problem will solve using Count function infront of name field,
eg. (select count(name) from login where name=@name)

Thank you.


reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Patil_Rakesh

According to the "Reddysankark" Error. He didn't use OUTPUT parameter when he call the procedure! Thats what I have given.




Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patil_Rakesh on: 4/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Pandians,
Okay sir, i got it.
but sir,
rather than using output parameter , He can use Select statement for returning a value.
Thanks

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
No, Instead of using Resultset (i.e: using SELECT statement for returning anything as result set). Its better using OUTPUT parameter :)

So, In this case, Using OUTPUT parameter is a best one!

In-case, If you want multiple rows then You have to go with SELECT (result set)

Thanks for your understanding!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patil_Rakesh on: 4/30/2012 [Member] Starter | Points: 25

Up
0
Down
Now it is clear,
thank you sir.

reddysankark-13471, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response