我想根据用户的输入动态生成返回的select语句，如@s_scope=‘aa#ss#’
生成的是select * from aa where code='aa' or code='ss'
我的select语句可否先是一个字符串形式，因为我要依据@s_scope的内容动态来写select语句，高手帮帮忙！！！
CREATE PROCEDURE sp_aa
@s_scope varchar(1000)
AS
select * from aa where
GO
谢谢！！！
---------------------------------------------------------------
CREATE PROCEDURE sp_aa
@s_scope varchar(1000)
AS
declare @strsql varchar(8000)
select @strsql=''
select @strsql='select * from aa where code='"+replace(left(@s_scope,len(@s_scope)-1),'#',''' or code =''')+''''
exec(@strsql)
GO
未测试的，先试试。
---------------------------------------------------------------
CREATE PROCEDURE sp_aa
@s_scope varchar(1000)
AS
declare @sql varchar(2000)
set @s_scope=''''+left(@s_scope,len(@s_scope)-1)+''''
set @sql='select * from aa where code in ('+ replace(@s_scope,'#',''',''') +')'
exec (@sql)
GO
---------------------------------------------------------------
CREATE PROCEDURE sp_aa
@s_scope varchar(1000)
AS
create table #t(code varchar(50))
set @s_scope=ltrim(rtrim(@s_scope))
declare @i1 int
declare @i2 int
declare @code varchar(50)
set @i1=1
set @i2=charindex('#',@s_scope)-1
while @i2>0
begin
set @code=substring(@s_scope,@i1,@i2-@i1+1)
insert into #t values(@code)
set @i1=@i2+2
set @i2=charindex('#',@s_scope,@i1)-1
end
select a.* from #t as t join aa as a on t.code=a.code
drop table #t
go
---------------------------------------------------------------
用sp_executesql代替exec吧﹐那樣更好﹐注意@sql變量要定義為nvarchar
---------------------------------------------------------------
同意楼上的，sp_executesql效率高，比较灵活
具体优点可以看帮助：）