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

[cooling]

--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 行）

[cooling]

我要实现的功能是，根据过程提供的参数，动态的生成sql查询语句，同时要有返回值。

[zjcxc]

--不能用表变量(因为作用域的问题),用临时表就行了.

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

[zjcxc]

--上面的错误还没改完

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]

--其实楼主可以直接显示的.

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

[victorycyz]

同意邹健的最后的修改方案。

不过，如果只是为了测试表变量的使用，我觉得是不是把@tb作为输入参数就行了？

[zjcxc]

我测试过,sp_executesql不支持用表变量做参数