Click here to Skip to main content
15,906,625 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
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 the procedure its showing error.please help me for this
Posted
Updated 28-Apr-12 19:38pm
v2
Comments
Sandeep Mewara 29-Apr-12 1:39am    
What error?

Hi ,
You have to Execute procedure with out parameter like this

SQL
DECLARE @OutValue int
EXECUTE CheckUserName 'sankar',@OutValue OUT
SELECT @OutValue

Best Regards
M.Mitwalli
 
Share this answer
 
Comments
Sandeep Mewara 29-Apr-12 2:11am    
Now, this looks like a recursive SP! :)

CheckUserName calling CheckUserName? or CheckUserName SP called in some other SP? Neh.... one SP would do the job.
Mohamed Mitwalli 29-Apr-12 2:21am    
Could you check this link :)
http://www.sqlteam.com/article/stored-procedures-returning-data
Sandeep Mewara 29-Apr-12 2:39am    
:)
I agree that call of SP will be as you say. but I guess, the above statement if missed will not give any error in SP as the out parameter was not set. It would just not do the job as it intend to.
Mohamed Mitwalli 29-Apr-12 2:22am    
see the section for Using OUTPUT variables .
Sandeep Mewara 29-Apr-12 2:40am    
Here take a 5 for pointing the wrong syntax!
SQL
set @pid=(select name from login where name=@name);

Is 'name' of int type? I doubt that as in your where clause you compare name with @name (that is varchar)

So, either ways, if name is int type then your where clause would throw an error OR if name is varchar type then your set would throw an error.
Correct the datatype comparison/assignment.
 
Share this answer
 
Comments
Mohamed Mitwalli 29-Apr-12 2:00am    
Hi sandeep the problem he have on executing he used out parameter so he hvae to declare it and pass this parameter to procedure and then select it .
see my solution .
Sandeep Mewara 29-Apr-12 2:09am    
Did you read what I have written here? Does that make any sense?
Mohamed Mitwalli 29-Apr-12 2:21am    
yes i agree with you on your solution , but on his post his execute procedure in wrong way "execute CheckUserName 'sankar'"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900