How to get sp_executesql to accept parameter values that are not nvarchar

Question

I'm trying to build a dynamic SQL statement that is within a stored procedure. Here is a simplified version of it:

CREATE PROC dbo.GetOrders
    @UserID INT = 2
AS
    DECLARE @SQLString NVARCHAR(MAX)

    SET @SQLString = N'(
                 SELECT * FROM dbo.Orders WHERE UserID = '+@UserID+'
                 )
    EXEC sys.sp_executesql @SQLString

The problem I have is that sp_executesql only works with Unicode data. So I get a conversion error on the @UserID parameter that's an integer:

Conversion failed when converting the nvarchar value 'SELECT * FROM dbo.Orders WHERE UserID = '

I MUST have my parameters declared at the start of the stored procedure and for user's to supply those values. The examples I've seen so far of using sp_executesql are showing the parameters and their values as being defined at runtime of the sp_executesql. This won't work for me because I need to reuse the parameters in other areas of the same stored procedure.

How could I solve this without specifying all my parameters to be of type nvarchar?

Answer 1

try this...

CREATE PROC dbo.GetOrders
@UserID INT = 2
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @SQLString NVARCHAR(MAX);

    SET @SQLString = N' SELECT * FROM dbo.Orders ' 
                   + N' WHERE UserID = @UserID '


EXEC sp_executesql @SQLString
                   ,N'@UserID INT'
                   ,@UserID
END

Or simply use the following

CREATE PROC dbo.GetOrders
@UserID INT = 2
AS
BEGIN
  SET NOCOUNT ON;

 SELECT * FROM dbo.Orders 
 WHERE UserID = @UserID 
END