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   gt    BeginDate  and CONVERT date  datesent   lt    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   gt      BeginDate    and CONVERT date  datesent   lt      EndDate   and RequestType ID in      RequestTypeID     group by FileStream ID  SentToLab ID    EXEC  statement       Operand type clash  date is incompatible with int

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] EXEC sp_executesql with multiple parameters

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to sp-executesql