如何转换字符串为sql语句？？？sqlserver 存储过程中

[蝈蝈俊]

declare
@AddInProductIdsStr as varchar(100),

select @AddInProductIdsStr = '9,10,11'

SELECT * FROM A WHERE A.B IN (@AddInProductIdsStr)

其中A表的B字段是int型的
这个sql 语句如何写？？

我是在存储过程中用到的，AddInProductIdsStr 是存储过程传入的参数，因为这个数不确定多少，只能用字符串，不能用int型

[rwq_]

declare
@AddInProductIdsStr as varchar(100),

select @AddInProductIdsStr = '9,10,11'

SELECT * FROM A WHERE patindex('%'+convert(varchar(10),a.b)+'%',@AddInProductIdsStr)

[hzhiguo]

declare @AddInProductIdsStr as varchar(100)
Declare @ExecStr Varchar(255)
/*select @AddInProductIdsStr = '9,10,11'
SELECT * FROM A WHERE A.B IN (@AddInProductIdsStr)
....
*/

Select @ExecStr=' SELECT * From A where A.B IN '+@AddInProductIdsStr
--一定要保证@AddInProductIdsStr不为空，此处生省略

Exec(@ExecStr)

[QQ576006]

SELECT * FROM A WHERE patindex('%'+cast(a.b as integer)+'%',@AddInProductIdsStr)

[蝈蝈俊]

/*
前台购物车相关商品说明列表存储过程
*/
create procedure Buy_Basket_Preferential_Product_Str
@MainProductId as int,
@AddInProductIdsStr as varchar(100),
@AddInNum as int,
@ReturnStr as varchar(1000) output
as
declare
@MainProductName as varchar(500),
@MainProductmarket_price as money,
@MainProductcsdn_price as money,
@MainProductmember_price as money


select @MainProductName = product_name,@MainProductmarket_price = market_price,
@MainProductcsdn_price = csdn_price,@MainProductmember_price = member_price
from buy_product where product_id = @MainProductId

declare
@AddInProductName as varchar(500),
@AddInProductmarket_price as money,
@AddInProductcsdn_price as money,
@AddInProductmember_price as money



select @ReturnStr = ''
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '<AddInProducts MainProductName="'+ltrim(rtrim(@MainProductName))+'" AddInSelectNum="'+ltrim(rtrim(convert(varchar(8),@AddInNum)))+'">'

declare
@aa as varchar(255)
select @aa ='select product_name,market_price,csdn_price,member_price from buy_product where product_id in ('+ ltrim(rtrim(@AddInProductIdsStr)) +');'


/*查询购物车中所有商品有没有关联优惠品*/
declare mycursor scroll cursor for
exec(@aa)

if (@@fetch_status != 0 )
begin
open mycursor
fetch first from mycursor into @AddInProductName,@AddInProductmarket_price,@AddInProductcsdn_price,@AddInProductmember_price
while @@fetch_status = 0
begin
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '<product id='+ltrim(rtrim(convert(varchar(8),@MainProductId)))+'>'
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '<productname>'+ltrim(rtrim(@AddInProductName))+'</productname>'
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '<marketprice>'+ltrim(rtrim(@AddInProductName))+'</marketprice>'
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '<csdnprice>'+ltrim(rtrim(@AddInProductName))+'</csdnprice>'
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '<memberprice>'+ltrim(rtrim(@AddInProductName))+'</memberprice>'
select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '</product>'

fetch NEXT from mycursor into @AddInProductName,@AddInProductmarket_price,@AddInProductcsdn_price,@AddInProductmember_price
end
end
close mycursor
deallocate mycursor

select @ReturnStr = ltrim(rtrim(@ReturnStr)) + '</AddInProducts>'

[蝈蝈俊]

Server: Msg 156, Level 15, State 1, Procedure Buy_Basket_Preferential_Product_Str, Line 39
Incorrect syntax near the keyword 'exec'.


to hzhiguo(蝈蝈)

exec(@aa) 就没法使用游标了。

[蝈蝈俊]

这一个就没有问题：

declare @aa as varchar(300)
select @aa = ' select product_name,market_price,csdn_price,member_price from buy_product where product_id in ('+'10,11,12'+')'
exec(@aa)

[hzhiguo]

--select * from T_product
Declare @exestr varchar(255)
Declare @sql varchar(50)
Declare @product_id char(20)
Declare @product_name varchar(50)
select @sql='Select product_id,product_name From T_Product'

Select @exestr='Declare mycursor scroll cursor for '+ @sql
exec( @exestr)
Open mycursor
Fetch mycursor into @product_id, @product_name
While(@@Fetch_Status=0)
Begin
Select @product_name
Fetch mycursor into @product_id,@product_name
End

close mycursor
Deallocate mycursor

[lihaitaoami]

declare
@aa as varchar(255),
@tmpstr_exec varchar(255)
select @aa ='select product_name,market_price,csdn_price,member_price from buy_product where product_id in ('+ ltrim(rtrim(@AddInProductIdsStr)) +');'

/*查询购物车中所有商品有没有关联优惠品*/
select @tmpstr_exec='declare mycursor scroll cursor for '+@aa
exec(@tmpstr_exec)
应该可以搞定了。

[blackfiles]

试试

[蝈蝈俊]

非常感谢，搞定了