declare @sql varchar(300),@lastqty decimal(18,3),@lastprice decimal(18,3),@month varchar(2)--这个是动态月份
set @sql='select @lastqty = Stock'+@month +',@lastprice = Price'+@month+' from YMStock where matcode='Z0001''
-- select @lastqty=Stock10,@lastprice= Price10 from YMStock where matcode='Z0001'
--因为
exec (@sql)
select @lastqty,@lastprice--提示Must declare the variable '@lastqty'.
我应该怎样写让哪两个变量有值出来
set @sql='select @lastqty = Stock'+@month +',@lastprice = Price'+@month+' from YMStock where matcode='Z0001''
-- select @lastqty=Stock10,@lastprice= Price10 from YMStock where matcode='Z0001'
--因为
exec (@sql)
select @lastqty,@lastprice--提示Must declare the variable '@lastqty'.
我应该怎样写让哪两个变量有值出来
set @sql='select @lastqty = Stock'+@month +',@lastprice = Price'+@month+' from YMStock where matcode='Z0001''
-- select @lastqty=Stock10,@lastprice= Price10 from YMStock where matcode='Z0001'
--因为
exec ('DECLARE @lastqty decimal(18,3);'+@sql+';SELECT @lastqty')
declare @sql varchar(300),@lastqty nvarchar(20),@lastprice decimal(18,3),@month varchar(2)--这个是动态月份
set @sql='select '+ @lastqty +' = Stock'+@month +','+@lastprice +' = Price'+@month+' from YMStock where matcode=Z0001'''
print @sql
肯定会报错的
set @sql=N'select @lastqty = Stock'+@month +',@lastprice = Price'+@month+' from YMStock where matcode='Z0001''
-- select @lastqty=Stock10,@lastprice= Price10 from YMStock where matcode='Z0001'
--因为
SP_EXECUTESQL @sql,N'DECLARE @lastqty decimal(18,3)) OUTPUT ',@lastqty OUTPUT
select @lastqty,@lastprice
set @sql='select '+ LTRIM(@lastqty) +' = Stock'+@month +','+@lastprice +' = Price'+@month+' from YMStock where matcode=Z0001'''
print @sql
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
exec sp_executesql @sql,N'@MaterialCode varchar(50),@month varchar(2),@lastqty1 decimal(18,3) output, @lastprice1 decimal(18,3) output',
@MaterialCode ='E21550104',@month='02' ,@lastqty1=@lastqty output,@lastprice1=@lastprice output
select @lastqty,@lastprice
--这样没有值declare @sql nvarchar(300), @lastqty decimal(18,3),@lastprice decimal(18,3)
--,@month varchar(2)--这个是动态月份 Stock10表示取上个月数据,10月份的数据set @sql=N'select @lastqty1=Stock10, @lastprice1=Price10 from YMStock where MaterialCode = @MaterialCode'
exec sp_executesql @sql,N'@MaterialCode varchar(50),lastqty1 decimal(18,3) output, @lastprice1 decimal(18,3) output',
@MaterialCode ='E21550104',@lastqty1=@lastqty output,@lastprice1=@lastprice output
select @lastqty,@lastprice
如果去掉@month 这样就有值,可我想得到动态的月份,不想写死