CREATE procedure UTC_duijiang_pro1
(
@firstDate varchar(20),
@endDate varchar(20) )
as
declare @sqlstr varchar(255)
if exists (select * from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end
begin
select @sqlStr =' create view UTC_duijiang_view1 as (select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '+@firstDate+' and '+ @endDate+' and flag != 3)' exec(@sqlstr)
end
测试exec UTC_duijiang_pro1 '2002-2-12' , '2009-12-20'报错
Line 1: Incorrect syntax near 'TT'.
(
@firstDate varchar(20),
@endDate varchar(20) )
as
declare @sqlstr varchar(255)
if exists (select * from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end
begin
select @sqlStr =' create view UTC_duijiang_view1 as (select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '+@firstDate+' and '+ @endDate+' and flag != 3)' exec(@sqlstr)
end
测试exec UTC_duijiang_pro1 '2002-2-12' , '2009-12-20'报错
Line 1: Incorrect syntax near 'TT'.
解决方案 »
- 对下面代码情况不理解,望各位仁兄相助
- 如何剔除掉在之前月份曾经出现过的记录的SQL语句
- 如何让字段名包含有数字
- 一个超难实现的数据统计问题,请高手们来帮帮忙啊!!!
- 这是我从某一个项目经理的分析文档中关于数据表结构定义的部分抄录,请大家帮我分析一下她的水平!
- 请问该怎么写这条sql语句?
- sql server管理器和查询分析器一旦打开,这两个工具在任务管理器中就未响应,要等很久,其他程序无此现象
- SQL经典问题求解! 高手进!
- 全新win2k pro系统安装sql出现错误:"以前的某个程序安装已在安装计算机上创建挂起的文件操作...."
- 帮帮我 :( 如何在互联网上连接远程主机?
- 行转列问题
- 触发器问题,大家帮忙看看
@sqlstr 如果過長的話,用 Select @sqlstr=@sqlstr+''
用Print(@sql) 看看你拼接的SQL語句是否正確.
from Display_Acc_View as a
where Acctime between '+@firstDate+' and '+ @endDate+' and flag != 3 楼主在查询分析器里面测试一下上面的查询能否执行
(
@firstDate varchar(20),
@endDate varchar(20) )
as
begin
declare @sqlstr varchar(255)
if exists (select * from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end
begin
select @sqlStr =' create view UTC_duijiang_view1 as (select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '''+@firstDate+''' and '''+ @endDate+''' and flag != 3)'
exec(@sqlstr)
end
end
go--测试 exec UTC_duijiang_pro1 '2002-2-12' , '2009-12-20'
CREATE procedure UTC_duijiang_pro1
(
@firstDate varchar(20),
@endDate varchar(20) )
as
declare @sqlstr varchar(255)
if exists (select * from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end
begin
select @sqlStr =' create view UTC_duijiang_view1 as select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '''+@firstDate+''' and '''+ @endDate+''' and flag != 3'
exec(@sqlstr)
end
(
@firstDate varchar(20),
@endDate varchar(20) )
as
begin --加
declare @sqlstr varchar(255)
if exists (select * from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end
begin
select @sqlStr =' create view UTC_duijiang_view1 as (select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '''+@firstDate+''' and '''+ @endDate+''' and flag != 3)' --单引号变为三个引号
exec(@sqlstr)
end
end --加
go--测试 exec UTC_duijiang_pro1 '2002-2-12' , '2009-12-20'
@sqlstr 长度不够被截断了
把
declare @sqlstr varchar(255)
更改为
declare @sqlstr varchar(max)
就可以了
更改之后alter procedure UTC_duijiang_pro1
(
@firstDate varchar(20),
@endDate varchar(20) )
as
begin
declare @sqlstr varchar(max)
if exists (select 1 from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end select @sqlStr =' create view UTC_duijiang_view1 as (select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '''+@firstDate+''' and '''+ @endDate+''' and flag != 3)'
exec(@sqlstr)
end
from Display_Acc_View这句执行可以出结果
alter procedure UTC_duijiang_pro1
(
@firstDate varchar(20),
@endDate varchar(20) )
as
begin
declare @sqlstr varchar(max)
if exists (select 1 from sysobjects where id = object_id(N'[dbo].[UTC_duijiang _view1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view UTC_duijiang _view1'
exec(@sqlstr)
end select @sqlStr =' create view UTC_duijiang_view1 as (select a.CustCode,a.CustName,a.outloc,(a.塑料瓶盖数量+a.跨区盖数量) as PET,a.玻璃瓶盖数量 as RB,a.九盎司糖浆包数量 AS NB,a.十二盎司糖浆包数量 AS TB,a.九盎司糖浆桶数量 AS NT,a.十二盎司糖浆桶数量 AS TT
from Display_Acc_View as a
where Acctime between '''+@firstDate+''' and '''+ @endDate+''' and flag != 3)'
print(@sqlstr)
endexec UTC_duijiang_pro1 '2002-2-12' , '2009-12-20' 先看看你的sql語句,看是否有錯?
尤其是 yangsnow_rain_wind 谢谢你