Is this the correct way to use sp_executesql in a stored procedure?

Question

I have this huge stored procedure where I am building a sql statement. At the end, I create a parameter list like this:

DOES @paramList need and N in front of it??

SELECT @paramList = '@param1 nvarchar(300),
           @param2 nvarchar(10),
    @param3 nvarchar(10),
    @param4 nvarchar(100),
    @param5 nvarchar(1000),
       @param6 nvarchar(1000),
    @param7 nvarchar(200),
    @param8 nvarchar(2000)’

I am then calling sp_executesql like this:

EXEC sp_executesql @sql, @paramList,
  @param1,@param2,@param3,@param4,
             @param5,@param6,@param7,@param8

where @sql is the statement, paramList is my parameter list and all the other variables are the parameters I declared at the top of the stored procedure. Is this the correct way to do it?

Answer 1

Yes, that's the right way. You can pass the '@param1 nvarchar(300), ..., @param8 nvarchar(2000)' dirrectly to sp_executesql, no need to pass it through @paramList. Also, the second argument to sp_executesql is an unicode type, so you probably do some extra ascii-to-unicode conversations. You could do straight:

EXEC sp_executesql @sql, 
     N'@param1 nvarchar(300),...,@param8 nvarchar(2000)', 
     @param1,...,@param8;

Is it better to keep the parameter list declaration in the call itself, because is so tightly coupled with the actual parameters passed.

Answer 2

If the string does not use characters outside the database collation, the N'' notation is not required.

You did not state the declaration of @paramList. N'' would only be useful if it is declared NVARCHAR.

SQL server automatically takes care of conversion between VARCHAR and NVARCHAR values (again with regard to the db collation).

So, in your case, the solution works. The "correct" way would be to use NVARCHAR and N'', but in this case is not required.