sp_executesql 函数使用
0
悬赏园豆：10
[已解决问题]
解决于 2009-09-12 15:06
-- 我想传递时间为什么不行呢，请问各位大哥怎么实现，小弟先谢了
DECLARE @str AS NVARCHAR(max);
DECLARE @ParmDefinition AS NVARCHAR(MAX)
SET @str = N'SELECT * FROM dbo.Orders WHERE RequiredDate BETWEEN ''@s'' AND ''@e''';
SET @ParmDefinition = N'@s DATETIME,@e DATETIME';
DECLARE @startdate AS nvarchar
DECLARE @enddate AS nvarchar
SET @startdate = '2008-06-1';
SET @enddate ='2008-09-31';
PRINT @str;
DECLARE @ss AS NVARCHAR(max);
set @ss = N'@s = @startdate ,@e= @enddate';
EXECUTE sp_executesql @str, @ParmDefinition, @ss;
问题补充：
USE Northwind
DECLARE @str AS NVARCHAR(max);
DECLARE @startdate AS NVARCHAR(20)
DECLARE @enddate AS NVARCHAR(20)
SET @startdate = '';
SET @enddate = '';
SET @startdate = '2008-06-1';
SET @enddate ='2008-09-30';
SET @str = N'SELECT OrderID FROM dbo.Orders WHERE RequiredDate BETWEEN @startdate AND @enddate';
EXECUTE sp_executesql @str
这样也不行啊！
最佳答案
0
最上面的语句没有定义 DECLARE @startdate AS nvarchar的长度，所以始终为1
补充答案时你把变量@startdate放在单引号之内是无效的
试试这个
DECLARE @str AS NVARCHAR(max);
DECLARE @ParmDefinition AS NVARCHAR(MAX)
DECLARE @startdate AS nvarchar(16)
DECLARE @enddate AS nvarchar (16)
SET @startdate = '1996-01-01';
SET @enddate ='1996-12-31';
print @startdate
SET @str = N'SELECT * FROM dbo.Orders WHERE RequiredDate BETWEEN '''+@startdate +''' AND '''+@enddate+ ''' '
print @str
EXECUTE sp_executesql @str
如果想按真正的日期比较，可以用cast
DECLARE @str AS NVARCHAR(max);
DECLARE @ParmDefinition AS NVARCHAR(MAX)
DECLARE @startdate AS nvarchar(16)
DECLARE @enddate AS nvarchar (16)
SET @startdate = '1996-01-01';
SET @enddate ='1996-12-31';
print @startdate
SET @str = N'SELECT * FROM dbo.Orders WHERE RequiredDate BETWEEN cast('''+@startdate +''' as datetime) AND cast('''+@enddate+ ''' as datetime) '
print @str
EXECUTE sp_executesql @str
或者效率更高一些，直接用日期比较
DECLARE @str AS NVARCHAR(max);
DECLARE @ParmDefinition AS NVARCHAR(MAX)
DECLARE @startdate AS datetime
DECLARE @enddate AS datetime
SET @startdate = cast('1996-01-01' as datetime)
SET @enddate =cast('1996-12-31' as datetime)
print @startdate
print @enddate
select* from dbo.Orders where RequiredDate BETWEEN @startdate and @enddate
收获园豆：10
谢谢 大哥邀月了，第二种方法不是我想要的。
其他回答(1)
0
DECLARE @str AS NVARCHAR(max);
DECLARE @ParmDefinition AS NVARCHAR(MAX)
DECLARE @startdate AS nvarchar
DECLARE @enddate AS nvarchar
SET @startdate = '2008-06-1';
SET @enddate ='2008-09-31';
SET @str = N'SELECT * FROM dbo.Orders WHERE RequiredDate BETWEEN @startdate AND '@enddate ';
EXECUTE sp_executesql @str
这样不是更省事情啊