SQL语句:set @execStr='select @s1=max('+@fieldName+') from '+@tableName 在语句 execute

G

gondsoft

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-23

• #1

SQL语句:set @execStr='select @s1=max('+@fieldName+') from '+@tableName 在语句 execute(@execStr) 执行时出错,详细代码...(100分)<br />declare @TableName nvarchar(254),@FieldName nvarchar(254),
@execStr nvarchar(254),@s1 char(20)
--定义变量
select @TableName='t1',@FieldName='f1' --给变量赋值
set @execStr='select @s1=max('+@fieldName+') from '+@tableName
execute(@execStr)
出现错误:
Must declare the variable '@s1'.
在@execStr中加入declare @s1 char(20)就可运行,我希望此变量中的查询结果能够赋值到
变量@s1中,该如何解决..?

 

E

EricssonT20

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-23

• #2

建立只有一条记录的表存放@s1的值，再把它取出来赋给@s1。

create table tab_tmp_maxvalue(tmp_id int, tmp_s1 char(20))
go

insert into tab_tmp_maxvalue(tmp_id, tmp_s1) values(1, 'abc')
go

declare @TableName nvarchar(254),@FieldName nvarchar(254),
@execStr nvarchar(254),@s1 char(20)
--定义变量
select @TableName='employee',@FieldName='emp_id' --给变量赋值
set @execStr='update tab_tmp_maxvalue
set tmp_s1=t.vmax
from (select max('+@FieldName+') as vmax from '+@TableName + ') as t
where tmp_id=1'
execute(@execStr)
select @s1=tmp_s1 from tab_tmp_maxvalue
select @s1
go

 

磊

磊

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-24

• #3

[brown]在@execStr中加入declare @s1 char(20)就可运行,我希望此变量中的查询结果能够赋值到
变量@s1中[/brown]

这句话意思不很清楚。

 

G

gondsoft

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-24

• #4

需要在变量@execStr中再次定义declare @s1 char(20),而@s1在代码的开始处已经定义.

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-24

• #5

declare @TableName nvarchar(254),@FieldName nvarchar(254),
@execStr nvarchar(254),@s1 char(20)output
--定义变量
select @TableName='t1',@FieldName='f1' --给变量赋值
set @execStr='select'+ @s1+'=max('+@fieldName+') from '+@tableName
execute(@execStr)

 

B

book523

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-24

• #6

你的sql写法有问题！你试一试这个
@execStr='select '+@s1+'=max('+@fieldName+') from '+@tableName
execute(@execStr)

 

G

gondsoft

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-24

• #7

>>ugvanxk:
Cannot use the OUTPUT option in a DECLARE statement.在DECLARE语句中不能使用
OUTPUT选项
>>ugvanxk and book523:
'select '+@s1+'=max('+@fieldName+') from '+@tableName 变量@s1在执行时将其
中的值加入语句中,引起错误...

 

P

Pearl.

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-24

• #8

看来只能用cursor了

set @execstr='declare cursor cr as select max('+@fieldname+') from '+@tablename
exec(@execstr)
open cr
fetch next into @s1
close cr
deallocate cr

 

G

gondsoft

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-25

• #9

感谢Pearl的帮助.
你在
1.set @execstr='declare cursor cr as select max('+@fieldname+') from '+@tablename
其中的as应该为for
2.fetch next into @s1
应该为 fetch from cr into @s1

 

G

gondsoft

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-05-25

• #10

Pearl.外其他人各5'