带有多个参数的 EXEC sp_executesql [英] EXEC sp_executesql with multiple parameters

问题描述

如何正确地将参数传递给EXEC sp_executesql语句?

这就是我现在所拥有的，但我遇到了错误:

alter PROCEDURE [dbo].[usp_getReceivedCases]-- 这里添加存储过程的参数@LabID 整数，@RequestTypeID varchar(max),@BeginDate 日期，@EndDate 日期作为开始-- 添加了 SET NOCOUNT ON 以防止额外的结果集-- 干扰 SELECT 语句.设置无计数；声明@statement nvarchar(4000)set @statement = N'select SentToLab,来自 dbo.vEmailSentWHERE SentToLab_ID=@LabID 和 convert(date,DateSent) >= @BeginDate和 CONVERT(date, datesent) <= @EndDate和 RequestType_ID ( @RequestTypeID )EXEC sp_executesql @statement,N'@LabID int', @LabID, N'@BeginDate date', @BeginDate,N'@EndDate date', @EndDate, @RequestTypeID=@RequestTypeID结尾

RequestTypeID 是一个以逗号分隔的整数列表，例如:1,2,3,4,5"

这是我的尝试 #2，也没有成功

声明@statement nvarchar(4000)SET @statement =' 选择 SentToLab_ID来自 dbo.vEmailSent在哪里SentToLab_ID='+@LabID+' and convert(date,DateSent) >='+@BeginDate +'和 CONVERT(date, datesent) <= '+@EndDate+'和 RequestType_ID 在 ('+ @RequestTypeID+' )按 FileStream_ID、SentToLab_ID' 分组执行(@statement)

<块引用>

操作数类型冲突:日期与整数不兼容

解决方案

这是一个简单的例子:

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

你的电话是这样的

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

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

解决方案

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

这篇关于带有多个参数的 EXEC sp_executesql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！