0

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?

Duc Nha
  • 3
  • 1
  • 2
  • 1
    what lengths are you declaring for your nvarchar column and parameter? Does it match? – Stuart Ainsworth Mar 05 '15 at 16:20
  • column length is nvarchar(50), but @n is just nvarchar (no length) – Duc Nha Mar 05 '15 at 16:24
  • 1
    Add the length to your procedure. Otherwise it uses the default length for varchar. Sadly the default length can vary based on the usage which is why you ALWAYS need to specify the length of your varchars. FYI, as a parameter it is 1. – Sean Lange Mar 05 '15 at 16:29

1 Answers1

1

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
Community
  • 1
  • 1
Dan Field
  • 20,885
  • 5
  • 55
  • 71
  • You don't need to copy this to a local variable inside your proc. And you don't need the begin/end either. – Sean Lange Mar 05 '15 at 16:30
  • It will run much more slowly if you don't copy it to a local variable. And BEGIN and END are good practice, but yes they're not technically required. – Dan Field Mar 05 '15 at 16:31
  • @SeanLange Whilst the `BEGIN...END` construct is optional, personally I would always put it in there. Better to be explicit, than implicit with your object definitions. I've seen a few devs accidentally squeeze an additional line or two of code in to their sproc definitions by accident! – gvee Mar 05 '15 at 16:32
  • @DanField "It will run much more slowly if you don't copy it to a local variable..." - can you elaborate on this, please? Are you referring to parameter sniffing, or..? – gvee Mar 05 '15 at 16:33
  • I disagree that begin and end are good practice. This is like adding extra { } in c# code. It does not prevent putting those extra lines in your procedure. You can still add lines after the final end. – Sean Lange Mar 05 '15 at 16:37
  • Using local variables IS a workaround for parameter sniffing, but I don't know if I'd consider it a "best practice". At best, it's a solution in search of a problem; at worst, it has it's own problems. http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx – Stuart Ainsworth Mar 05 '15 at 18:04