DB_51aspx/2014年/存储过程or函数/存储过程-关键词查询.sql

微信扫码关注公众号，代码整理好后第一时间通知你。

-- =============================================
-- Author:	李朴
-- Create date: 2014-2-8
-- Description:	通过关键词，查询该微帐号对应的规则表主键Id以及回复的类型
-- =============================================
alter PROCEDURE p_query_keywords 
	@wid int,
    @keywords varchar(500),
    @id int output,
    @responseType int output,
    @modelFunctionName varchar(200) output,
    @modelFunctionId  int output
AS
BEGIN
	set @id=0
	set @responseType=0
	set @modelFunctionId=0
	set @modelFunctionName=''
	 select top 1 @id=id,@responseType=responseType,@modelFunctionName=modelFunctionName,@modelFunctionId=modelFunctionId from wx_requestRule  where wid=@wid  and reqestType=1 and ( reqKeywords like @keywords+'|%' or reqKeywords='%|'+@keywords or reqKeywords like '%|'+@keywords+'|%' or reqKeywords=@keywords) order by createdate desc
     if @id=0
      begin
		select top 1 @id=id,@responseType=responseType,@modelFunctionName=modelFunctionName,@modelFunctionId=modelFunctionId from wx_requestRule  where wid=@wid  and reqestType=1 and reqKeywords like '%'+@keywords+'%'  order by createdate desc
		if @id=0
		begin
			select top 1 @id=id,@responseType=responseType,@modelFunctionName=modelFunctionName,@modelFunctionId=modelFunctionId from wx_requestRule  where wid=@wid  and reqestType=0 order by createdate desc
		end
	  end
     if @modelFunctionId is null
		begin
			set @modelFunctionId=0
		end
END
GO


------测试-----------------------
declare @id int
declare @responseType int
declare @modelFunctionName varchar(200)
declare @modelFunctionId int 
exec p_query_keywords 5,'首页|图文|看看222',@id output,@responseType output,@modelFunctionName output,@modelFunctionId output

--print @modelFunctionId

print  convert(varchar,@id) +'---'+ convert(varchar,@responseType)
+'----'+convert(varchar,@modelFunctionName)+'---'+convert(varchar,@modelFunctionId)