Assorted Gotchas

After wasting an entire day debugging one stored procedure, let me share my newly gained wisdom

1) When declaring nvarchar variables never forget to specify the size. If you don't it defaults to one then you will wonder why

select * from users where username =@username

returns nothing

2) When you have a stored procedure that calls another, the latter of which returns a value in an ouput paramer, do it like this

declare @ProductID int

exec [Products.Insert] 1,'ACME', 
'A generic ACME', 1, @ProductID output

select @ProductID

and not like this

declare @ProductID int

execute [Products.Insert] 1,'ACME', 
'A generic ACME', 1, @ProductID 
select @ProductID

The difference is the OUTPUT. If you fail to include that little word the output parameter is never populated!