sql stored procedure failing to retrieve 1

[Maldini]

Hi,

Hope I'm posting this in the right place.

This is my SP:
CREATE PROCEDURE GetDeptNum
(
@Name As NVarChar(70),
@ID As Int Output
)
AS
Select @ID=ID From Dept Where Name=@Name
GO

The problem is that its not retrieving the ID as I need to returning it, rather its always giving a DBNull, even when I copy the NVarChar string from my database and use it as input on my interface!!
I've tried using Like, tried trimming the input string and using like but the output is still a null.
Any pointers? Could this have to do with my string ebing comprised of asian characters and english?

Thanks

 

[NathanGriffiths]

You aren't returning your output parameter from the procedure. Try this;

CREATE PROCEDURE GetDeptNum
(
    @Name As NVarChar(70),
    @ID As Int Output
)
AS
    Select @ID=ID From Dept Where Name=@Name
    
    Return @ID
GO

Nathan

----------------------------------------
Pessimists, we're told, look at a glass containing 50% air and 50% water and see it as half empty. Optimists, in contrast, see it as half full. DBAs, of course, understand the glass is twice as big as it needs to be.
----------------------------------------

 

[Maldini]

Thanks for the reply.

But for some strange reason, the SP is still only getting DBNulls out of the database. If I go for something like Name=Name just as a check, it manages to retrieve an ID number.

 

[NathanGriffiths]

If you run this code;

declare @Name As NVarChar(70),  @ID As Int 

set @Name = N'<some name here>'

Select @ID=ID From Dept Where Name=@Name

Select @ID

does it return an ID number? If so I think the problem may be with the way you are handling the output parameters of the stored proc rather than the SP itself. Is SP called from some code?

Nathan

----------------------------------------
Pessimists, we're told, look at a glass containing 50% air and 50% water and see it as half empty. Optimists, in contrast, see it as half full. DBAs, of course, understand the glass is twice as big as it needs to be.
----------------------------------------

 

[Maldini]

Just tried that out and noticed that the problem is indeed with the value being passed into my SP.
Fixed it by conact-ing "%" to my input string.

Thanks for your help.