首页 新闻 搜索 专区 学院

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 这样也不行啊!
jackyong的主页 jackyong | 初学一级 | 园豆:149
提问于:2009-09-12 00:02
< >
分享
最佳答案
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
邀月 | 高人七级 |园豆:25475 | 2009-09-12 08:49
谢谢 大哥邀月了,第二种方法不是我想要的。
jackyong | 园豆:149 (初学一级) | 2009-09-12 15:04
其他回答(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

这样不是更省事情啊

温景良(Jason) | 园豆:150 (初学一级) | 2009-09-12 01:13
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册