col1 like '%'+@a+'%' and col2 like '%'+@b+'%' and col3 like '%'+@c+'%'........
declare @a varchar(50),@string varchar(8000),@i int set @i=1 set @string='' set @a='xzn' while @i<=len(@a) begin select @string=@string+'yourcol like ''%'+substring(@a,@i,1)+'%'' and ' select @i=@i+1 end select @string='select * from youtable where '+left(@string,len(@string)-3) print @string exec(@string)
declare @var varchar(1000) --变量 set @var = '1233333333asdfasdf' declare @sql varchar(8000),@i int, @len int select @i = 0, @len = len(@var)set @sql = 'select * from 表 where ' while @i < @len begin set @i = @i + 1 set @sql = @sql + ' and 字段 like ''%' + substring( @var, @i, 1 ) + '%''' end stuff( @sql, 1, 3, '' )exec ( @sql )
declare @a varchar(50),@string varchar(8000),@i int set @i=1 set @string='' set @a='xzn' --你所提交的字符串 while @i<=len(@a) begin select @string=@string+'yourcol like ''%'+substring(@a,@i,1)+'%'' and ' select @i=@i+1 end select @string='select * from youtable where '+left(@string,len(@string)-3) print @string exec(@string)
declare @string varchar(8000),@i int,@search nvarchar(100) set @search='lkjoajid' set @i=0 set @string='' while @i<len(@search) begin set @string=@string+'game like ''%' + substring(@search,@i,1) + '%'' and' set @i=@i+1 end set @page=(SELECT COUNT(id) FROM game WHERE '+@string+' (checked = 1) AND (del = 0) AND (type = 0) AND (tp = 0))有错误,好像是'+@string+'错了,请问应该怎么写?
--如果是这样的话,没有必要拆分,也没有必要用动态sql的--这样处理 declare @str varchar(1000) set @str='abcd' --要查询的变量--格式化处理 declare @i int,@re varchar(8000) select @i=len(@str),@re=@str while @i>1 select @re=stuff(@re,@i,0,',') ,@i=@i-1 select @re='%'+quotename(@re)+'%'--查询 select * from 表 where 字段 like @re
--也可以专门写一个自定义函数,实现格式化成like字符串--函数 create function f_formatstr( @str Nvarchar(2000) )returns Nvarchar(4000) as begin declare @re nvarchar(4000),@i int select @i=len(@str),@re=@str while @i>1 select @re=stuff(@re,@i,0,',') ,@i=@i-1 return('%'+quotename(@re)+'%') end go--调用函数实现查询 select * from( select name='abc' union all select '表' union all select '1a3' union all select '223' union all select '55c' union all select '67' union all select '88' )a where name like dbo.f_formatstr('abcd8') go--删除测试 drop function f_formatstr /*--测试结果name ---- abc 1a3 55c 88(所影响的行数为 4 行) --*/
set @i=1
set @string=''
set @a='xzn'
while @i<=len(@a)
begin
select @string=@string+'yourcol like ''%'+substring(@a,@i,1)+'%'' and '
select @i=@i+1
end
select @string='select * from youtable where '+left(@string,len(@string)-3)
print @string
exec(@string)
set @var = '1233333333asdfasdf' declare @sql varchar(8000),@i int, @len int
select @i = 0, @len = len(@var)set @sql = 'select * from 表 where '
while @i < @len
begin
set @i = @i + 1
set @sql = @sql + ' and 字段 like ''%' + substring( @var, @i, 1 ) + '%'''
end
stuff( @sql, 1, 3, '' )exec ( @sql )
set @i=1
set @string=''
set @a='xzn' --你所提交的字符串
while @i<=len(@a)
begin
select @string=@string+'yourcol like ''%'+substring(@a,@i,1)+'%'' and '
select @i=@i+1
end
select @string='select * from youtable where '+left(@string,len(@string)-3)
print @string
exec(@string)
set @search='lkjoajid'
set @i=0
set @string=''
while @i<len(@search)
begin
set @string=@string+'game like ''%' + substring(@search,@i,1) + '%'' and'
set @i=@i+1
end
set @page=(SELECT COUNT(id)
FROM game
WHERE '+@string+' (checked = 1) AND (del = 0) AND
(type = 0) AND (tp = 0))有错误,好像是'+@string+'错了,请问应该怎么写?
declare @str varchar(1000)
set @str='abcd' --要查询的变量--格式化处理
declare @i int,@re varchar(8000)
select @i=len(@str),@re=@str
while @i>1
select @re=stuff(@re,@i,0,',')
,@i=@i-1
select @re='%'+quotename(@re)+'%'--查询
select * from 表
where 字段 like @re
create function f_formatstr(
@str Nvarchar(2000)
)returns Nvarchar(4000)
as
begin
declare @re nvarchar(4000),@i int
select @i=len(@str),@re=@str
while @i>1
select @re=stuff(@re,@i,0,',')
,@i=@i-1
return('%'+quotename(@re)+'%')
end
go--调用函数实现查询
select *
from(
select name='abc'
union all select '表'
union all select '1a3'
union all select '223'
union all select '55c'
union all select '67'
union all select '88'
)a where name like dbo.f_formatstr('abcd8')
go--删除测试
drop function f_formatstr /*--测试结果name
----
abc
1a3
55c
88(所影响的行数为 4 行)
--*/
我在存储过程中定义了一个@page 为output,但是我在程序中使用的时候却一定要给@page传个值才能使用,不传值就会报错,说过程需要@page,但未提供该参数,这是怎么回事?我其它的存储过程也有output变量,都好用的,就这个必须传个值才能用,不解,请高手指点.
@page int output,@search nvarchar(100),@searchbk int,@searchlx varchar(10)
AS上面是存储过程的开头部分,第一个就是@page int output,应该没错吧.sqlcomm.CommandText = "searchglmjpage"
sqlcomm.Parameters.Add("@search", SqlDbType.NVarChar, 100).Value = Replace(Trim(Request("search")), "'", "")
sqlcomm.Parameters.Add("@searchlx", SqlDbType.VarChar, 10).Value = searchlx
sqlcomm.Parameters.Add("@page", SqlDbType.Int).Value = 0 '这里加上.value=后面赋个值就好用,去掉.value=0就报错了.而我在用其它的存储过程的时候都没有出现过这个问题.
sqlcomm.Parameters("@page").Direction = ParameterDirection.InputOutput
sqlcomm.ExecuteNonQuery()
Dim countpage
countpage = sqlcomm.Parameters("@page").Value
这段是调用存储过程的代码(VB.NET),看了半天也没发现问题,还请高手帮忙.