SET @sql_word= 'insert into '+@ResultTable +'
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
where EXITSTATION = '+@Station+'
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
print(@sql_word)
exec(@sql_word)如果我的@Station 为空,这个代码便会报错,请问怎样表现才会让我@Station 变量为空时跳过这行?
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
where EXITSTATION = '+@Station+'
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
print(@sql_word)
exec(@sql_word)如果我的@Station 为空,这个代码便会报错,请问怎样表现才会让我@Station 变量为空时跳过这行?
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
where EXITSTATION = '+ ISNULL(@Station,'EXITSTATION') +'
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
print(@sql_word)
似乎不行:
insert into Plan200902
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from AUDIT..exit2009020050303
where EXITSTATION =
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll
消息 156,级别 15,状态 1,第 5 行
关键字 'GROUP' 附近有语法错误。
如何使用呢,我是个初学者。
if @Station is not null
begin
你的sql
end
如何使用呢,我是个初学者。
if @Station is not null
begin
你的sql
end
if @Station <>''
begin
你的sql
end
想问一下这段代码需要标在单引号中么,我把他放在引号中的话无法运行。不是啊,把你需要判断的sql加到这个if里面就行了 就可以实现你要的判断是否为空
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
if @Station <>''
begin
WHERE EXITSTATION = '+@Station+'
end
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
print(@sql_word)
exec(@sql_word)
if @Station <>''
begin
SET @sql_word= 'insert into '+@ResultTable +'
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
where EXITSTATION = '+@Station+'
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
end就这样改。再试试
+' select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll '
+' from '+@BaseTable
+' where 1=1 '+case when @Station<>'' then ' and EXITSTATION='''+@Station+''' ' else '' end
+' GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll '
+' order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll 'print(@sql_word)
exec(@sql_word)
似乎不行:
insert into Plan200902
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from AUDIT..exit2009020050303
where EXITSTATION =
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll
消息 156,级别 15,状态 1,第 5 行
关键字 'GROUP' 附近有语法错误。
你这个不是null而是等于空的字符串改一下就可以了SET @sql_word= 'insert into '+@ResultTable +'
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
where EXITSTATION = '+ CASE ISNULL(@Station,'')
WHEN '' THEN 'EXITSTATION'
ELSE @Station END +'
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
似乎不行:
insert into Plan200902
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from AUDIT..exit2009020050303
where EXITSTATION =
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll
消息 156,级别 15,状态 1,第 5 行
关键字 'GROUP' 附近有语法错误。
你这个不是null而是等于空的字符串改一下就可以了SET @sql_word= 'insert into '+@ResultTable +'
select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll
from '+@BaseTable+'
where EXITSTATION = '+ CASE ISNULL(@Station,'')
WHEN '' THEN 'EXITSTATION'
ELSE @Station END +'
GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll
order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll'
很奇怪,这次表都无法创建出来了……
想问一下,比如我要在下面添加
and Recordtype&@Record=@Record
我当如何添加呢,两段话之间是用什么链接呢?
where 1=1
似乎不可以,想问一下。
set @sql_word = ''SET @sql_word=' insert into '+@ResultTable
+' select distinct EXITSTATION,11,exitdate,RECORDTYPE,vehicleclass,totaltoll '
+' from '+@BaseTable
if IsNull(@Station,'') > ''
begin
SET @sql_word=@sql_word+' where EXITSTATION = '''+@Station+''''--这里要注意
end
SET @sql_word=@sql_word+' GROUP BY exitdate,EXITSTATION,RECORDTYPE,vehicleclass,totaltoll '
+' order by EXITSTATION,exitdate,RECORDTYPE,vehicleclass,totaltoll '
print(@sql_word)
exec(@sql_word)
没必要 where 1=1吧