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?

score 1 · Answer 1 · answered May 12 '15 at 23:22

1

It's not clear why you are using dynamic SQL here at all but this is the correct approach.

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, N'@UserID int', @UserID= @UserID

answered May 12 '15 at 23:22

Martin Smith

438,706
87
741
845

So everytime I want to run sp_executesql I have to re-declare the parameters and set their values again? I simplified my example down to the minimum. The real stored procedure has about 10-15 parameters. And the sp_executesql command resides in many places (due to IF/ELSE branches). – volume one May 13 '15 at 08:53

score 1 · Accepted Answer · answered May 12 '15 at 23:24

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

answered May 12 '15 at 23:24

M.Ali

67,945
13
101
127

So everytime I want to run sp_executesql I have to re-declare the parameters and set their values again? I simplified my example down to the minimum. The real stored procedure has about 10-15 parameters. And the sp_executesql command resides in many places (due to IF/ELSE branches) – volume one May 13 '15 at 08:53
Yes, you will need to redefine the parameters for sp_executesql separately, because it has its own scope, anything declared outside of its scope is not visible to sp_executesql. – M.Ali May 13 '15 at 12:25
I see. As a workaround I've stored the parameter definitions in a variable as such `@SQLParamDef = N'@UserID int, @OrderID int, etc'` and just re-use that variable wherever sp_executesql needed. Seems to work! – volume one May 13 '15 at 14:33

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

2 Answers2