在存储过程中top为传进来的可变值怎么拼接才不报错?ALTER PROCEDURE [dbo].[sp_DDK_ElecGoods]
@changeNum int --购买的数量
select top @changeNum @_cards=(case when @_cards='' then GiftCardNo else (@_cards+','+GiftCardNo) end ) from dbo.ElecGoods
where ElecGoodType=@ElecGoodType and GiftCardStatus=1 and GiftCardDistributionStatus=0 top @changeNum这样使用不对,那应该怎么用呢?还是根本上就不能这样子用?
@changeNum int --购买的数量
select top @changeNum @_cards=(case when @_cards='' then GiftCardNo else (@_cards+','+GiftCardNo) end ) from dbo.ElecGoods
where ElecGoodType=@ElecGoodType and GiftCardStatus=1 and GiftCardDistributionStatus=0 top @changeNum这样使用不对,那应该怎么用呢?还是根本上就不能这样子用?
alter PROCEDURE [dbo].[sp_DDK_ElecGoods]
(
@changeNum int, --购买的数量
@_cards varchar(300),--这个变量你没有定义?
@ElecGoodType varchar(300)--这个变量你也没有定义?
)
as
begin
declare @sql varchar(8000)
set @sql='select top '+ltrim(@changeNum)+ ' '+@_cards+'=(case when '+@_cards+
'='''' then GiftCardNo else ('+@_cards+''',''+GiftCardNo) end ) from dbo.ElecGoods
where ElecGoodType='+@ElecGoodType+' and GiftCardStatus=1 and GiftCardDistributionStatus=0 '
exec(@sql)
end类似于这样
insert into test0805
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5declare @i int;set @i=2
select top (@i) * from test0805
/*
id
-----------
1
2
*/
字段名是不能使用变量@_cardsr的,不是top @changeNum 的问题
select top (@v) * from table
select top (@v) * from table