CODEWITHSUNDEEP
sql-serverstored-procedures
Duc Nha
Duc Nha

Reputation: 3

SQL server stored procedure query result empty

I have a test table named tblTest with two columns: name (nvarchar), age (tinyint), with values: 'My name', 10

Then, I create the following procedure:

create procedure procTest @n nvarchar as
select * from tblTest where name=@n

When I run this procedure, it return empty result: 

exec procTest @n='My name'

But when I change procedure to 

alter procedure procTest @n tinyint as
select * from tblTest where age=@n

and run 

exec procTest @n=10

It return one row

So, what happen here? Do I miss anything?

Upvotes: 0

Views: 1876

Answers (1)

Dan Field
Dan Field

Reputation: 21641

Add a length spec to your nvarchar in the stored procedure:

create procedure procTest (@n nvarchar(50)) as
select * from tblTest where name=@n

Copying it to a local variable will improve performance, and is generally good practice in stored procedures (see SQL Server: Query fast, but slow from procedure for more information) - as is using BEGIN and END statements:

create procedure procTest (@n nvarchar(50)) as
BEGIN
DECLARE @name nvarchar(50) = @n;
select * from tblTest where name=@name  
END

Upvotes: 1

Related Questions