请教一个从指定名称的临时表取出指定nid的protectid的存储过程的写法

[icefire988]

有一个表
id pid
1 2
1 3
1 4
1 5
1 6
2 1
2 2
2 3
想有存储过程实现以下功能：从表中查询出指定的id的pid的记录插入到一个指定的临时表；临时表的主建为递增的（从1开始）;再从临时表中取出指定主建是某数字的pid。

如上表我先把id为1的记录查入到指定的临时表#t,再去出主建（假设为nid）为3的记录，结果取出的应该是4
if exists(select name from sysobjects where name = 'protectid' and type = 'p')
drop procedure protectid
go
create procedure protectid
@username nvarchar(50),
@numid bigint,
@protectid nvarchar(12) output
as

declare @tbname nvarchar(50)
set @tbname=quotename('#'+@username)

if object_id(@tbname) is not null exec('drop table '+@tbname)

exec('select nnid=identity(bigint,1,1), protectid into'+ @tbname+'from productdet')
exec('select'+ @protectid +'=protectid from '+ @tbname +'where nnid='+@numid)

GO

declare @sprotectid nvarchar(12)
exec protectid 't','3',@sprotectid output

我这样写，提示错误
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '=' 附近有语法错误。
该怎么写，谢谢！

[orcale]

create procedure protectid
@username nvarchar(50),
@numid bigint,
@protectid nvarchar(12) output
改成:
create procedure protectid(
@username nvarchar(50),
@numid bigint,
@protectid nvarchar(12) output)

[yjzhg]

exec('select '+ @protectid +'=protectid from '+ @tbname +'where nnid='+@numid)

[icefire988]

谢谢楼上两位，不过还是不行呀！

[xluzhong]

在执行之前先print出来看看sql又没有错

exec('select'+ @protectid +'=protectid from '+ @tbname +'where nnid='+@numid)

你的结果是:如@protestid为test(少了空格)
selecttest=protectid from ......

[子陌红尘]

if exists(select name from sysobjects where name = 'protectid' and type = 'p')
drop procedure protectid
go
create procedure protectid
@username nvarchar(50),
@numid bigint,
@protectid nvarchar(12) output
as

declare @sql nvarchar(4000)
declare @tbname nvarchar(50)
set @tbname=quotename('#'+@username)

if object_id(@tbname) is not null exec('drop table '+@tbname)

set @sql = N'select identity(bigint,1,1) as nnid,protectid into '+ @tbname+N' from productdet'
set @sql = @sql + N'
'
set @sql = @sql + N'select @protectid=protectid from '+ @tbname +N' where nnid='+@numid

exec sp_execute @sql,
N'@protectid nvarchar(12) out',
@protectid out

GO

[paoluo]

红尘写的还是有点问题，我改了下，现在可以OK了。

[paoluo]

--建立测试环境
Create table productdet
(id Int,
protectid Int)
--插入数据
Insert productdet Values(1, 2)
Insert productdet Values(1, 3)
Insert productdet Values(1, 4)
Insert productdet Values(1, 5)
Insert productdet Values(1, 6)
Insert productdet Values(2, 1)
Insert productdet Values(2, 2)
Insert productdet Values(2, 3)
GO
--建立存储过程
if exists(select name from sysobjects where name = 'protectid' and type = 'p')
drop procedure protectid
go
create procedure protectid
@username nvarchar(50),
@numid bigint,
@protectid nvarchar(12) output
as

declare @sql nvarchar(4000)
declare @tbname nvarchar(50)
set @tbname=quotename('#'+@username)

if object_id(@tbname) is not null exec('drop table '+@tbname)

set @sql = N'select identity(bigint,1,1) as nnid,protectid into '+ @tbname+N' from productdet'
set @sql = @sql + N'
'
set @sql = @sql + N'select @protectid=Convert(nvarchar(12),protectid) from '+ @tbname +N' where nnid='+RTrim(@numid) --这里要用Rtrim处理一下，且得到@protectid时要转换为nvarchar

exec sp_executesql @sql, --是用sp_executesql，不是sp_execute
N'@protectid nvarchar(12) out',
@protectid out

GO
--测试
declare @sprotectid nvarchar(12)
exec protectid 't',3,@sprotectid output
Select @sprotectid
GO
--删除测试环境
Drop table productdet
Drop procedure protectid
--结果
/*
4
*/

[icefire988]

非常感谢！
我还想问一下
set @sql = N'select identity(bigint,1,1) as nnid,protectid into '+ @tbname+N' from productdet'
set @sql = @sql + N'
'
set @sql = @sql + N'select @protectid=Convert(nvarchar(12),protectid) from '+ @tbname +N' where nnid='+RTrim(@numid)
特别是
set @sql = @sql + N'
'
怎么解释？是固定格式？

[paoluo]

只是换行而已，你加空格也可以的。



--建立测试环境
Create table productdet
(id Int,
protectid Int)
--插入数据
Insert productdet Values(1, 2)
Insert productdet Values(1, 3)
Insert productdet Values(1, 4)
Insert productdet Values(1, 5)
Insert productdet Values(1, 6)
Insert productdet Values(2, 1)
Insert productdet Values(2, 2)
Insert productdet Values(2, 3)
GO
--建立存储过程
if exists(select name from sysobjects where name = 'protectid' and type = 'p')
drop procedure protectid
go
create procedure protectid
@username nvarchar(50),
@numid bigint,
@protectid nvarchar(12) output
as

declare @sql nvarchar(4000)
declare @tbname nvarchar(50)
set @tbname=quotename('#'+@username)

if object_id(@tbname) is not null exec('drop table '+@tbname)

set @sql = N'select identity(bigint,1,1) as nnid,protectid into '+ @tbname+N' from productdet'
set @sql = @sql + N' '
set @sql = @sql + N'select @protectid=Convert(nvarchar(12),protectid) from '+ @tbname +N' where nnid='+RTrim(@numid)--这里要用Rtrim处理一下，且得到@protectid时要转换为nvarchar

exec sp_executesql @sql,--是用sp_executesql，不是sp_execute
N'@protectid nvarchar(12) out',
@protectid out

GO
--测试
declare @sprotectid nvarchar(12)
exec protectid 't',3,@sprotectid output
Select @sprotectid
GO
--删除测试环境
Drop table productdet
Drop procedure protectid
--结果
/*
4
*/