关于查询的时候常量和变量的问题

[rainvictor]

declare @begin_date int,@end_date int
select @begin_date = 20090101,@end_date = 20090619
select b.date_id
from table1 as a
join table2 as b
on a.id= b.id
where (b.date_id between @begin_date and @end_date )

执行的时候会报错：当前命令发生了严重错误。应放弃任何可能产生的结果


如果改成
declare @begin_date int,@end_date int
select @begin_date = 20090101,@end_date = 20090619
select b.date_id
from table1 as a
join table2 as b
on a.id= b.id
where (b.date_id between @begin_date and 20090619)

就执行正常了

请教是什么原因？？？急！

[sunhonglei2004]

看定义的变量的数据类型啊

到底应该是int 还是varchar啊

[rainvictor]

带上'号还是一样的错误

[rainvictor]

date_id的数据类型也是int

[sunhonglei2004]

没有看出有啥问题来

[sunhonglei2004]

declare @begin_date int,@end_date int
set @begin_date = 20090101
set @end_date = 20090619
select b.date_id
from table1 as a
join table2 as b
on a.id= b.id
where (b.date_id between @begin_date and @end_date)

[rainvictor]

需要补充下的是 两个表的记录数都是在100万条左右，有没有可能是这方面的原因造成的

[rainvictor]

有没有在sql查询的时候遇到过
如果用变量查询报错 把这个变量替换成某个常量后 就没有问题了

这个应该也是这样的问题
不知道是什么原因引起的 以及该如何解决

[fuhaojie626]

declare @begin_date int,@end_date int
select @begin_date = 20090101,@end_date = 20090619
select b.date_id
from table1 as a
join table2 as b
on a.id= b.id
where (b.date_id between @begin_date and @end_date )



where 后面不要括号试试

[rainvictor]

不是括号的问题

[feixianxxx]

关注@

[feixianxxx]

declare @begin_date int,@end_date int 
select @begin_date = 20090101
select @end_date = 20090619 
select b.date_id 
from table1 as a 
join table2 as b 
  on a.id= b.id 
where b.date_id between @begin_date and @end_date 

个人感觉 不会错~也许版本问题

[rainvictor]

sql 2005的

[ai_li7758521]

DECLARE @T1 TABLE(id int,name char)
insert @T1
SELECT 1,'t' UNION ALL
SELECT 2,'r' UNION ALL 
SELECT 3,'c'

DECLARE @T2 TABLE(id int,date_id int)
insert @T2
SELECT 1,20090101 UNION ALL
SELECT 3,20080101 UNION ALL 
SELECT 2,20091101 UNION ALL
SELECT 3,20090701 UNION ALL 
SELECT 6,20090101 UNION ALL 
SELECT 2,20090201

--经测试，这种表达没问题
declare @begin_date int,@end_date int 
select @begin_date = 20090101,@end_date = 20090619 

select b.date_id 
from @T1 as a 
join @T2 as b 
  on a.id= b.id 
where (b.date_id between @begin_date and @end_date )
/* 
date_id
-----------
20090101
20090201

(2 行受影响)*/
----------------------------------------------
----------------------------------------------
declare @begin_date int,@end_date int 
select @begin_date = 20090101,@end_date = 20090619 

select b.date_id 
from @T1 as a 
join @T2 as b 
  on a.id= b.id 
where (b.date_id between @begin_date and 20090619) 
/*
date_id
-----------
20090101
20090201

(2 行受影响)*/

[ws_hgo]

我喜欢这样写

declare @begin_date int,@end_date int 
set @begin_date=20090101
set @end_date=20090619 
select b.date_id from table1 a join table2 b on a.id= b.id 
where b.date_id between @begin_date and @end_date

[hui_hui_2007]

date_id
在数据库表中是什么类型，是字符型吗？
为何不加引号呢？

[jueyingfd]

declare @begin_date int,@end_date int
set @begin_date=20090101
set @end_date=20090619
select b.date_id from table1 a join table2 b on a.id= b.id
where b.date_id between @begin_date and @end_date

[zzz1975]

常量和变量问题,这样应该不是这问题

[usher_gml]

declare @begin_date int,@end_date int 
set @begin_date=20090101
set @end_date=20090619 
select b.date_id from table1 a join table2 b on a.id= b.id 
where b.date_id between @begin_date and @end_date

个人觉得这样没问题

[nandorin]

SQL code

如何才能发类似的帖子,。求指教～

是不是直接从2005查询分析器复制过来的？

[JonasFeng]

[Quote=引用楼主 rainvictor 的帖子:]
declare @begin_date int,@end_date int
select @begin_date = 20090101,@end_date = 20090619
select b.date_id
from table1 as a
join table2 as b
on a.id= b.id
where (b.date_id between @begin_date and @end_date )

执行的时候会报错：当前命令发生了严重错误。应放弃任何可能产生的结果


如果改成
declare @begin_date int,@end_date int
select @begin_date = 20090101,@end_date = 2009061…
[/Quote]

要错都错。要对都对呀。不可能一句错，一句对。
如果20090619 加了引号，楼主那句也是对的。

楼主那一句改成这样就OK了。

between cast(@begin_date as varchar) and cast(@end_date  as varchar)