有一表 A
实绩 日期 编号 280.120 2006-03-06 15:32:34.837 1
290.153 2006-04-06 15:31:11.523 2
270.053 2007-05-06 15:30:20.947 3
260.175 2007-06-06 15:30:31.893 4请问以下语句有什么问题,应该如何改正?
declare @sqlstring char(800),@bh int
set @sqlstring='select * from A where 编号='+'(convert(char(10),@bh))'
set @bh=4
exec (@sqlstring)
实绩 日期 编号 280.120 2006-03-06 15:32:34.837 1
290.153 2006-04-06 15:31:11.523 2
270.053 2007-05-06 15:30:20.947 3
260.175 2007-06-06 15:30:31.893 4请问以下语句有什么问题,应该如何改正?
declare @sqlstring char(800),@bh int
set @sqlstring='select * from A where 编号='+'(convert(char(10),@bh))'
set @bh=4
exec (@sqlstring)
set @bh=4
set @sqlstring='select * from A where 编号='+rtrim(char(10),@bh)
exec (@sqlstring)
set @bh=4
set @sqlstring='select * from A where 编号='''+cast(@bh as varchar) + '''
exec (@sqlstring)
set @bh = 4select * from A where 编号=@bh
set @sqlstring='select * from A where 编号='''+(convert(char(10),@bh))+''''
set @bh=4
exec (@sqlstring)
一个存储过程能设置2个参数吗?
比如说我想CREATE一个存储过程
SELECT * FROM @TABLE_NAME WHERE 编号=@BIANHAO
我赋值@TABLE_NAME 是 A,@BIANHAO 是 4 就能得出等价于
SELECT * FROM A WHERE 编号=4 的结果
drop table tb
gocreate table tb
(
实绩 decimal(18,3),
日期 datetime,
编号 int
)insert into tb(实绩,日期,编号) values(280.120, '2006-03-06 15:32:34.837', 1)
insert into tb(实绩,日期,编号) values(290.153, '2006-04-06 15:31:11.523', 2)
insert into tb(实绩,日期,编号) values(270.053, '2007-05-06 15:30:20.947', 3)
insert into tb(实绩,日期,编号) values(260.175, '2007-06-06 15:30:31.893', 4)
godeclare @sqlstring char(800),@bh int
set @bh=4
set @sqlstring='select * from tb where 编号='+cast(@bh as varchar)
exec (@sqlstring)drop table tb/*result
实绩 日期 编号
-------------------- ----------------------- ----
260.175 2007-06-06 15:30:31.893 4
*/
dawugui(潇洒老乌龟) 运行得出这个Server: Msg 105, Level 15, State 1, Line 3
Unclosed quotation before the character string ''
exec (@sqlstring)
'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ''
exec (@sqlstring)
'.
可以了
但是为什么不能用CONVERT呢?
直接int
drop table tb
gocreate table tb
(
实绩 decimal(18,3),
日期 datetime,
编号 int
)insert into tb(实绩,日期,编号) values(280.120, '2006-03-06 15:32:34.837', 1)
insert into tb(实绩,日期,编号) values(290.153, '2006-04-06 15:31:11.523', 2)
insert into tb(实绩,日期,编号) values(270.053, '2007-05-06 15:30:20.947', 3)
insert into tb(实绩,日期,编号) values(260.175, '2007-06-06 15:30:31.893', 4)
godeclare @sqlstring char(800),@bh int
set @bh=4
set @sqlstring='select * from tb where 编号='+rtrim(@bh)exec (@sqlstring)
可以了
但是为什么不能用CONVERT呢?不是不能用而是你用的不对,你的写法只是把(convert(char(10),@bh))单程字符串了。declare @sqlstring char(800),@bh int
set @bh=4
set @sqlstring='select * from A where 编号='+(convert(char(10),@bh))
exec (@sqlstring)你这样在试试~~
谢谢了
把我把顺路再问个问题,嘎嘎
一个存储过程能设置2个参数吗?
比如说我想CREATE一个存储过程
SELECT * FROM @TABLE_NAME WHERE 编号=@BIANHAO
我赋值@TABLE_NAME 是 A,@BIANHAO 是 4 就能得出等价于
SELECT * FROM A WHERE 编号=4 的结果这个也解决下好不