create procedure usp_getitem
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
declare @sqlstr nvarchar(200),
@sqlstr = 'select '+ @olditemvalue + '=' + @item + 'from sal_base where empno = '+ '''+@empno+'''+ 'and '+ '''+@periodname+'''
exec(@sqlstr)
return @olditemvalue
我想得到传入的 @item的列值,用@olditemvalue 返回不知道怎么写?
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
declare @sqlstr nvarchar(200),
@sqlstr = 'select '+ @olditemvalue + '=' + @item + 'from sal_base where empno = '+ '''+@empno+'''+ 'and '+ '''+@periodname+'''
exec(@sqlstr)
return @olditemvalue
我想得到传入的 @item的列值,用@olditemvalue 返回不知道怎么写?
@olditemvalue int output ?
create procedure usp_getitem
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
declare @sqlstr nvarchar(200),
@sqlstr = N'select '+ @olditemvalue + '=' + @item + 'from sal_base where empno = '+ '''+@empno+'''+ 'and '+ '''+@periodname+'''
exec SP_EXECUTESQL @sqlstr,N'@olditemvalue INT,@item VARCHAR(20)',@olditemvalue OUTPUT,@ITEM
return @olditemvalue 这样试试?动态执行还不太懂
如果不考虑有多行记录的话用底下的试下:create procedure usp_getitem
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20),
)
as
begin
declare @sqlstr nvarchar(200)
create table #A(OldItemvalue int)
@sqlstr = 'select '+ @olditemvalue + '=' + @item + 'from sal_base where empno = '+ '''+@empno+'''+ 'and '+ '''+@periodname+'''
insert into #A
exec(@sqlstr)
select @olditemvalue = OldItemvalue from #A
return
end
我的这一行总是有错误能不能帮忙看看,这个存储过程是还有问题的
@sqlstr = 'select '+ @olditemvalue + ' = ' + @item + ' from sal_base where empno = ' + '''' + @empno + '''' + ' and '+ '''' + @periodname + '''' 你在“+ ' from ”的地方from前面最好要加个空格的,要不然有可能会和前面的变量重新组成一个字符串了,这样不就不对了?
2,用SP_EXECUTESQL
3,SQL语句的变量长度最好定义长一点,稍不注意就超过200了,而且要是nvarchar的
可大致修改一下(没有调试)
create procedure usp_getitem
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
declare @sqlstr nvarchar(400),
@sqlstr = N'select top 1 @olditemvalue = item from sal_base where empno = '+ '''+@empno+'''+ 'and periodname = '+ '''+@periodname+''' + 'and item='+ '''+@item +'''
exec SP_EXECUTESQL @sqlstr,@olditemvalue INT OUTPUT
-- SELECT @olditemvalue
-》
@olditemvalue = olditem
下面这个没有语法错误了,试一下create procedure usp_getitem
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
declare @sqlstr nvarchar(400),
@sqlstr = N'select top 1 @olditemvalue = olditem from sal_base where empno = '''+@empno+''' and periodname = '''+@periodname+''' and item='''+@item +''
exec SP_EXECUTESQL @sqlstr,@olditemvalue INT OUTPUT
-- SELECT @olditemvalue
select a from sal_base where b = 'g' and c= 'f'
其中 a,g,f是传入的值,我就是想得到a的值,只有一行数据。
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
declare @sqlstr nvarchar(400),
@sqlstr = N'select top 1 @olditemvalue = ' + @item + ' from sal_base where empno = '''+@empno+''' and periodname = '''+@periodname + ''
exec SP_EXECUTESQL @sqlstr,@olditemvalue INT OUTPUT
-- SELECT @olditemvalue
a,g,f都是传入的,那你select a还用条件干嘛啊?
声明本人对于存储过程认识很肤浅
set @sqlstr = 'select ' + @item + 'from sal_base where empno = '+ '''+@empno+'''+ 'and '+ '''+@periodname+'''
@item 为列名(数据表中的字段名)
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20)
)
as
begin
declare @sqlstr nvarchar(200)
create table #A(OldItemvalue int)
set @sqlstr = 'select '+ @olditemvalue + ' = ' + @item + ' from sal_base where empno = ' + '''' + @empno + '''' + ' and '+ '''' + @periodname + '''' insert into #A
exec(@sqlstr)
select @olditemvalue = OldItemvalue from #A
return
end
create procedure usp_getitem
(
@olditemvalue int output,
@item varchar(20),
@empno varchar(20),
@periodname varchar(20),
)
as
begin
declare @sqlstr nvarchar(200)
create table #A(OldItemvalue int)
@sqlstr = 'select '+ @olditemvalue + '=' + @item + 'from sal_base where empno = '+ '''+@empno+'''+ 'and '+ '''+@periodname+'''
print @sqlstr--把动态语句print出来看下是不是写错了
insert into #A exec(@sqlstr) --这里是把动态语句执行的结果插入临时表里面
select @olditemvalue = OldItemvalue from #A
return
end