SQL存储过程写法!
用的ASP+SQL,只会写不带参数的存储过程.
原来的程序是这样的.
- VB code
if key <> "" and lbid <> "" and isnumeric(lbid) then sql = "select * from market where (info like '%"&key&"%' or bt like '%"&key&"%' or huiyuan like '%"&key&"%' or name like '%"&key&"%') and lbid = " & lbid & " order y ding desc , sj1 desc"elseif key <> "" then sql = "select * from market where info like '%"&key&"%' or bt like '%"&key&"%' or huiyuan like '%"&key&"%' or name like '%"&key&"%' order by ding desc,sj1 desc"elseif lbid <> "" and isnumeric(lbid) then sql = "select * from market where lbid = " & lbid & " order by ding desc,sj1 desc"else sql = "select * from market order by ding desc,sj1 desc"end if
SQL存储过程该怎么写?
[解决办法]
试试:
存储过程:
- SQL code
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE getMarketByKeyLbID @key nvarchar(20), @lbid intASBEGIN SET NOCOUNT ON; if @key is not null and @lbid is not null select * from market where (info like '%'+ @key +'%' or huiyuan like '%'+ @key +'%' or [name] like '%'+ @key +'%') and lbid=@lbid order by ding desc else if @key is not null select * from market where info like '%'+ @key +'%' or huiyuan like '%'+ @key +'%' or [name] like '%'+ @key +'%' order by ding desc, sj1 desc else if @lbid is not null select * from market where lbid=@lbid order by ding desc, sj1 desc else select * from markey order by ding desc, sj1 descENDGO