EXEC sp_executesql with multiple parameters

Question

How to pass the parameters to the EXEC sp_executesql statement correctly?

This is what I have now, but i'm getting errors:

alter PROCEDURE [dbo].[usp_getReceivedCases]
    -- Add the parameters for the stored procedure here
    @LabID int,
    @RequestTypeID varchar(max),
    @BeginDate date,
    @EndDate date
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


declare @statement nvarchar(4000)

set @statement = N'select   SentToLab,
FROM     dbo.vEmailSent
WHERE     SentToLab_ID=@LabID and convert(date,DateSent) >= @BeginDate 
and CONVERT(date, datesent) <= @EndDate
and RequestType_ID in ( @RequestTypeID )

EXEC sp_executesql  @statement,N'@LabID int',  @LabID, N'@BeginDate date', @BeginDate,N'@EndDate date', @EndDate, @RequestTypeID=@RequestTypeID

END

RequestTypeID is a comma delimited list of integers, like so: "1,2,3,4,5"

here is my try #2, also unsuccessful

declare @statement nvarchar(4000)

SET @statement =' select    SentToLab_ID

FROM     dbo.vEmailSent
WHERE     
SentToLab_ID='+@LabID+' and convert(date,DateSent) >= '+@BeginDate +'
and CONVERT(date, datesent) <= '+@EndDate+'
and RequestType_ID in ('+ @RequestTypeID+' )
group by FileStream_ID, SentToLab_ID'


EXEC(@statement)

Operand type clash: date is incompatible with int

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm — Tab Alleman, Feb 12 '15 at 15:24
@TabAlleman - using method 1 i get: Conversion failed when converting the varchar value '+ @RequestTypeID+' to data type int. — Madam Zu Zu, Feb 12 '15 at 15:28

score 110 · Accepted Answer · edited Feb 12 '15 at 15:37

110

Here is a simple example:

EXEC sp_executesql @sql, N'@p1 INT, @p2 INT, @p3 INT', @p1, @p2, @p3;

Your call will be something like this

EXEC sp_executesql @statement, N'@LabID int, @BeginDate date, @EndDate date, @RequestTypeID varchar', @LabID, @BeginDate, @EndDate, @RequestTypeID

edited Feb 12 '15 at 15:37

marc_s

732,580
175
1,330
1,459

answered Feb 12 '15 at 15:25

SAS

3,943
2
27
48

6

It is almost the same when you need to assign a value of variable using `sp_executesql` - only add `OUTPUT` to the parameters definition list for the specified parameter and the parameter itself: `EXEC sp_executesql @sql, N'@p1 INT OUTPUT', @p1 OUTPUT;` – gotqn May 10 '17 at 12:25
What if the param is string example instead of IN query for integers if its string. It doesn't work for me nor did i found and solution on the internet. Do you have any clue? – Sudhir Jan 27 '23 at 15:42
I think that would require a solution that opens up for sql injection: SET "@SQLCmd = 'SELECT * FROM Table WHERE col IN (' + @cols + ')'" – SAS Mar 13 '23 at 14:13

score 8 · Answer 2 · answered Apr 24 '18 at 16:24

This also works....sometimes you may want to construct the definition of the parameters outside of the actual EXEC call.

DECLARE @Parmdef nvarchar (500)
DECLARE @SQL nvarchar (max)
DECLARE @xTxt1  nvarchar (100) = 'test1'
DECLARE @xTxt2  nvarchar (500) = 'test2' 
SET @parmdef = '@text1 nvarchar (100), @text2 nvarchar (500)'
SET @SQL = 'PRINT @text1 + '' '' + @text2'
EXEC sp_executeSQL @SQL, @Parmdef, @xTxt1, @xTxt2

score 2 · Answer 3 · answered Oct 07 '20 at 12:07

If one need to use the sp_executesql with OUTPUT variables:

EXEC sp_executesql @sql
                  ,N'@p0 INT'
                  ,N'@p1 INT OUTPUT'
                  ,N'@p2 VARCHAR(12) OUTPUT' 
                  ,@p0
                  ,@p1 OUTPUT
                  ,@p2 OUTPUT;

score -2 · Answer 4 · answered Dec 10 '19 at 01:12

-2

maybe this help :

declare 
@statement AS NVARCHAR(MAX)
,@text1 varchar(50)='hello'
,@text2 varchar(50)='world'

set @statement = '
select '''+@text1+''' + '' beautifull '' + ''' + @text2 + ''' 
'
exec sp_executesql @statement;

this is same as below :

select @text1 + ' beautifull ' + @text2

answered Dec 10 '19 at 01:12

Harry.wong

12
3

2

This post does not answer the question, nor does it contribute anything not covered in the two existing answers. – C8H10N4O2 Dec 10 '19 at 13:22

EXEC sp_executesql with multiple parameters

4 Answers4

Linked