存储过程中的动态查询语句

cooling 2003-12-07 05:40:35
--reading data

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'Proc_ReadingData'
AND type = 'P')
DROP PROCEDURE Proc_ReadingData
GO

CREATE PROCEDURE Proc_ReadingData
@ReadYearMonth Char(6),
@group smallint,
@team tinyint,
@serial tinyint

AS

begin

declare @sSql char(1024)

declare @tb table(
id int null,
usernum char(20) null,
usergroup smallint null,
userteam smallint null,
serial tinyint null,
readtime datetime null,
orireading int null,
reading int null,
curreading int null)

set @sSql='insert into ''@tb'' (id, usernum, usergroup, userteam, serial, readtime, orireading)'
set @sSql=@sSql + 'select r.id, a.usernum, a.usergroup, a.userteam, a.serial, r.readtime, r.orireading, r.reading'
set @sSql=@sSql + 'from ammeter a'
set @sSql=@sSql + 'join reading r'
set @sSql=@sSql + 'on a.usergroup = r.usergroup'
set @sSql=@sSql + 'and a.userteam = r.userteam'
set @sSql=@sSql + 'and a.serial = r.serial'

--if @ReadYearMonth <> '' then set @sSql=@sSql+' and '
--if @group <> 0 then ……
--这里要对条件判断,现在被我注释掉了??
set @sSql=@sSql + 'order by a.usergroup, a.userteam, a.serial, r.readtime'

Exec (@sSql)

select * from @tb

end

go


提示我:

服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '@tb' 附近有语法错误。

(所影响的行数为 0 行)
...全文
47 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2003-12-08
  • 打赏
  • 举报
回复
我测试过,sp_executesql不支持用表变量做参数
victorycyz 2003-12-08
  • 打赏
  • 举报
回复
同意邹健的最后的修改方案。

不过,如果只是为了测试表变量的使用,我觉得是不是把@tb作为输入参数就行了?
zjcxc 2003-12-07
  • 打赏
  • 举报
回复
--其实楼主可以直接显示的.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Proc_ReadingData' AND type='P')
DROP PROCEDURE Proc_ReadingData
GO

CREATE PROCEDURE Proc_ReadingData
@ReadYearMonth Char(6),
@group smallint,
@team tinyint,
@serial tinyint
AS
declare @sSql varchar(8000)
set @sSql='select r.id, a.usernum, a.usergroup, a.userteam, a.serial, r.readtime, r.orireading, r.reading'
+ 'from ammeter a join reading r'
+ 'on a.usergroup = r.usergroup'
+ 'and a.userteam = r.userteam'
+ 'and a.serial = r.serial'

--if @ReadYearMonth <> '' then set @sSql=@sSql+' and '
--if @group <> 0 then ……
--这里要对条件判断,现在被我注释掉了??
set @sSql=@sSql + 'order by a.usergroup, a.userteam, a.serial, r.readtime'
Exec (@sSql)
go
zjcxc 2003-12-07
  • 打赏
  • 举报
回复
--上面的错误还没改完

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Proc_ReadingData' AND type='P')
DROP PROCEDURE Proc_ReadingData
GO

CREATE PROCEDURE Proc_ReadingData
@ReadYearMonth Char(6),
@group smallint,
@team tinyint,
@serial tinyint
AS
declare @sSql varchar(8000)
create table #tb(
id int null,
usernum char(20) null,
usergroup smallint null,
userteam smallint null,
serial tinyint null,
readtime datetime null,
orireading int null,
reading int null,
curreading int null)

set @sSql='insert into #tb(id, usernum, usergroup, userteam, serial, readtime, orireading,reading)'
+ 'select r.id, a.usernum, a.usergroup, a.userteam, a.serial, r.readtime, r.orireading, r.reading'
+ 'from ammeter a join reading r'
+ 'on a.usergroup = r.usergroup'
+ 'and a.userteam = r.userteam'
+ 'and a.serial = r.serial'

--if @ReadYearMonth <> '' then set @sSql=@sSql+' and '
--if @group <> 0 then ……
--这里要对条件判断,现在被我注释掉了??
set @sSql=@sSql + 'order by a.usergroup, a.userteam, a.serial, r.readtime'
Exec (@sSql)
select * from #tb
go
zjcxc 2003-12-07
  • 打赏
  • 举报
回复
--不能用表变量(因为作用域的问题),用临时表就行了.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Proc_ReadingData' AND type='P')
DROP PROCEDURE Proc_ReadingData
GO

CREATE PROCEDURE Proc_ReadingData
@ReadYearMonth Char(6),
@group smallint,
@team tinyint,
@serial tinyint
AS
declare @sSql varchar(8000)
create table #tb table(
id int null,
usernum char(20) null,
usergroup smallint null,
userteam smallint null,
serial tinyint null,
readtime datetime null,
orireading int null,
reading int null,
curreading int null)

set @sSql='insert into #tb(id, usernum, usergroup, userteam, serial, readtime, orireading)'
set @sSql=@sSql + 'select r.id, a.usernum, a.usergroup, a.userteam, a.serial, r.readtime, r.orireading, r.reading'
set @sSql=@sSql + 'from ammeter a'
set @sSql=@sSql + 'join reading r'
set @sSql=@sSql + 'on a.usergroup = r.usergroup'
set @sSql=@sSql + 'and a.userteam = r.userteam'
set @sSql=@sSql + 'and a.serial = r.serial'

--if @ReadYearMonth <> '' then set @sSql=@sSql+' and '
--if @group <> 0 then ……
--这里要对条件判断,现在被我注释掉了??
set @sSql=@sSql + 'order by a.usergroup, a.userteam, a.serial, r.readtime'

Exec (@sSql)

select * from #tb

end

go
cooling 2003-12-07
  • 打赏
  • 举报
回复
我要实现的功能是,根据过程提供的参数,动态的生成sql查询语句,同时要有返回值。

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧