Store proc

Other SQL Server Topics
1

Please help to find why the following codes generates the error msg netween @ToyName = vToyName - Incorrect syntax near '='.

Create Procedure ReturnToyName (@ToyID char(4), @ToyName varchar(20)
Output)
AS
If exists (Select @ToyName = vToyName from trial where cToyID = @ToyID)
Return 1
Else
Return 2

2

Can't do EXISTS and ASSIGNMENT in the same statement.

Perhaps this?

Select @ToyName = vToyName from trial where cToyID = @ToyID
If @@ROWCOUNT >= 1
Return 1
Else
Return 2
1 Like
3
if (selec count(1) from trial where cToyID = @ToyID) > 1 print 'hhh' else print 'hohoho'
4

I have assumed that @ToyName is an OUTPUT parameter.

If not the assignment was a waste of time in the first place! and a straight EXISTS would have done ...

5

yes, n it worked.
Thank you

6

You need to be careful here! Specifically, you should explicitly set the value of @ToyName in the proc, otherwise it will retain its previous value if no matching row is found.

That is, if the receiving variable for @ToyName contains 'GI Joe' when the proc is called, it will still have that value if no matching name if found.

Typically you'd want to return NULL if no match was found, so:

Select @ToyName = NULL
Select @ToyName = vToyName from trial where cToyID = @ToyID
Return case when @@rowcount > 0 then 1 else 2 end