比如
create procedure
@aa varchar(10),
@bb varchar(10)
if aa<>''
begin
select * from table where @aa=@@bb
end
create procedure
@aa varchar(10),
@bb varchar(10)
if aa<>''
begin
select * from table where @aa=@@bb
end
create procedure
@aa varchar(10),
@bb varchar(10)
if aa<>''
begin
exec('select * from table where ' + @aa + '=' +@bb
end
@aa varchar(10),
@bb varchar(10)
if aa<>''
begin
exec('select * from table where ' + @aa + '=''' +@bb + '''')
end
@aa varchar(10),
@bb varchar(10)
if @aa<>''
begin
exec('select * from table where ' + @aa + '=''' +@bb + '''')
end
exec('select * from table where ' + @aa + '=''' +@bb + '''')
end
有个问题后变的三个'''这个是干什么的?SQL字符串不是用''括起来就好了么
后便那个@bb是可以直接用的有没有办法只把@aa变成动态的,我的语句比较长只用一个动态列
或者 select * from table where exec('@aa')=@bb
或者 select * from table where exec('@aa')=@bb
DECLARE @id VARCHAR(10)
SET @id='id'
EXEC('SELECT * FROM #tb t WHERE ' + @id +' =5') id model color level
----------- ----------- ----------- -----------
5 8 7 1(1 row(s) affected)
里边那个/怎么处理
--改为动态语句
create procedure
@aa varchar(10),
@bb varchar(10)
if aa<>''
begin
declare @sql nvarchar(500)
set @sql='select * from table where '+@aa+'=@bb';
exec sp_executesql @sql,N'@bb varvhar(10)',@bb
end
DECLARE @id VARCHAR(10)
SET @id='id'
EXEC('SELECT cast(color as varchar) + ''/''+ cast(model as varchar) FROM #tb t WHERE ' + @id +' =5')
-------------------------------------------------------------
7/8(1 row(s) affected)
@chazhao varchar(10),
@canshu varchar(40)
as
if @chazhao<>''
begin
exec('select a.danhao,a.name,a.riqi,a.jixing,a.type,
cast(a.num as varchar(12))+''/''+case when cast(o.num as varchar(12)) is null then ''0'' else cast(o.num as varchar(12)) end as uio,
case when cast(b.num as varchar(12)) is null then ''0'' else cast(b.num as varchar(12)) end+''/''+case when cast(c.num as varchar(12)) is null then ''0'' else cast(c.num as varchar(12)) end as fio,
case when a.num=o.num then '' else (cast(datediff(day,getdate(),dateadd(day,a.days,a.riqi)) as varchar(12))+''/''+cast(datediff(day,getdate(),dateadd(day,b.days,b.riqi)) as varchar(12))) end as ufdiff,
case when b.num=c.num then '' else cast(a.days as varchar(12))+''/''+cast(b.days as varchar(12)) end as ufdays
from
(select danhao,max(name) name,jixing,type,sum(num) num,max(riqi) riqi,max(days) days from repair_u_in where '+@chazhao+'=''+@canshu+'' group by jixing,type,danhao) as a
left join
(select danhao,refer,jixing,type,num,riqi from repair_u_out) as o
on a.danhao=o.refer and a.jixing=o.jixing and a.type=o.type
left join
(select danhao,refer,jixing,type,num,riqi,days from repair_fact_in) as b
on a.jixing=b.jixing and a.type=b.type and a.danhao=b.refer
left join
(select danhao,refer,jixing,type,num,riqi from repair_fact_out) as c
on b.danhao=c.refer and b.type=a.type and b.jixing=a.jixing ')
end
go
对数据类型而言运算符无效。运算符为 divide,类型为 varchar。(所影响的行数为 16 行)警告: 聚合或其它 SET 操作消除了空值。
全部贴出来吧求高手给改的可以正确执行
机器没有sqlserver,没法帮你改了。
begin
exec('select * from table where ' + @aa + '=''' +@bb + '''')
end
有个问题后变的三个'''这个是干什么的?SQL字符串不是用''括起来就好了么
后便那个@bb是可以直接用的有没有办法只把@aa变成动态的,我的语句比较长只用一个动态列
#10楼 得分:0回复于:2010-12-01 20:42:08引用 8 楼 theperfact 的回复:郁闷 又出了个新的问题。exec('select name+'/'+id from #tb where '+@aa+'='+@bb)
里边那个/怎么处理DECLARE @id VARCHAR(10)
SET @id='id'
EXEC('SELECT cast(color as varchar) + ''/''+ cast(model as varchar) FROM #tb t WHERE ' + @id +' =5') 仔细看了下答案都在这 我试出来了谢谢