stored procedure error in sql server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 28-Apr-12 19:17pm

sankarreddyece

Updated 28-Apr-12 19:38pm

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 29-Apr-12 1:39am

What error?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mohamed Mitwalli · Answer 1 · 2012-04-28T19:56:00

Solution 2

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

Posted 28-Apr-12 19:56pm

Mohamed Mitwalli

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!

Mohamed Mitwalli 29-Apr-12 3:06am

Thanks Sandeep :)

Sandeep Mewara · Answer 2 · 2012-04-28T19:42:00

Solution 1

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.

Posted 28-Apr-12 19:42pm

Sandeep Mewara

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'"