具有多个参数的EXEC sp_executesql [英] EXEC sp_executesql with multiple parameters
本文介绍了具有多个参数的EXEC sp_executesql的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将参数正确传递给EXEC sp_executesql
语句?
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是逗号分隔的整数列表,例如:"1,2,3,4,5"
RequestTypeID is a comma delimited list of integers, like so: "1,2,3,4,5"
这是我的尝试#2,也没有成功
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)
操作符类型冲突:日期与int不兼容
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;
您的电话将会是这样
EXEC sp_executesql @statement, N'@LabID int, @BeginDate date, @EndDate date, @RequestTypeID varchar', @LabID, @BeginDate, @EndDate, @RequestTypeID
这篇关于具有多个参数的EXEC sp_executesql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文