2.8w+
社区成员
写一个带参数的存储过程??
只有一个存储过程"pd_myselect"
有两个参数:参数1(颜色),参数2(码数)
如果选择"(颜色)"就把参数1传回"pd_myselect"的查询语句中,
如果选择"(码数)"就把参数2传回"pd_myselect"的查询语句中,
如果选择"(颜色)"和"(码数)"就把参数1,参数2传回"pd_myselect"的查询语句中
这个存储过程在SQL中怎么写?请教各位朋友.
---------- 代替下面的-(ASP)----
wstr="where"
if styles<>empty and styles<>"All" then wstr=wstr&" Styles ='"& styles &"' and"
if material<>empty and material<>"All" then wstr=wstr&" Material ='"& material &"' and"
if right(wstr,3)="and" then wstr=left(wstr,len(wstr)-3)
if len(wstr)=5 then wstr=""
strSQL = "SELECT * FROM products "&wstr
_____________________--
有两个参数:参数1(颜色),参数2(码数)
如果选择"(颜色)"就把参数1传回"pd_myselect"的查询语句中,
如果选择"(码数)"就把参数2传回"pd_myselect"的查询语句中,
如果选择"(颜色)"和"(码数)"就把参数1,参数2传回"pd_myselect"的查询语句中
这个存储过程在SQL中怎么写?请教各位朋友.
---------- 代替下面的-(ASP)----
wstr="where"
if styles<>empty and styles<>"All" then wstr=wstr&" Styles ='"& styles &"' and"
if material<>empty and material<>"All" then wstr=wstr&" Material ='"& material &"' and"
if right(wstr,3)="and" then wstr=left(wstr,len(wstr)-3)
if len(wstr)=5 then wstr=""
strSQL = "SELECT * FROM products "&wstr
_____________________--
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
sywcf 2004-12-10
收藏
lienzhu 2004-12-07
CREATE PROC myproc (@Styles varchar(50)='',@Material varchar(50)='') AS
if @Styles='' and @Material<>''
SELECT * FROM products where Material =@Material
else if @Styles<>'' and @Material=''
SELECT * FROM products where Styles =@Styles
else if @Styles<>'' and @Material<>''
SELECT * FROM products where Styles =@Styles and Material =@Material
GO
if @Styles='' and @Material<>''
SELECT * FROM products where Material =@Material
else if @Styles<>'' and @Material=''
SELECT * FROM products where Styles =@Styles
else if @Styles<>'' and @Material<>''
SELECT * FROM products where Styles =@Styles and Material =@Material
GO
SuperBJack 2004-12-07
问题解决:
dim objCnn,objCmd,objRS,wstr
wstr="where"
if styles<>empty and styles<>"All" then wstr=wstr&" Styles ='"& styles &"' and"
if material<>empty and material<>"All" then wstr=wstr&" Material ='"& material &"' and"
if stone<>empty and stone<>"All" then wstr=wstr&" Stone ='"& stone &"' and"
if sex<>empty and sex<>"All" then wstr=wstr&" Sex ='"& sex &"' and"
if DesigNo<>empty then wstr=wstr&" Desino ='"& DesigNo &"'"
if right(wstr,3)="and" then wstr=left(wstr,len(wstr)-3)
if len(wstr)=5 then wstr=""
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "driver={sql server};server=XX_zh;uid=sa;pwd=123456789;database=MyoaData;"
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="pd_mySelect"
objCmd.Commandtype=adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@wstr",adVarChar,adParamInput,200)
objCmd("@wstr")= wstr
set objRS=objCmd.Execute
dim objCnn,objCmd,objRS,wstr
wstr="where"
if styles<>empty and styles<>"All" then wstr=wstr&" Styles ='"& styles &"' and"
if material<>empty and material<>"All" then wstr=wstr&" Material ='"& material &"' and"
if stone<>empty and stone<>"All" then wstr=wstr&" Stone ='"& stone &"' and"
if sex<>empty and sex<>"All" then wstr=wstr&" Sex ='"& sex &"' and"
if DesigNo<>empty then wstr=wstr&" Desino ='"& DesigNo &"'"
if right(wstr,3)="and" then wstr=left(wstr,len(wstr)-3)
if len(wstr)=5 then wstr=""
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "driver={sql server};server=XX_zh;uid=sa;pwd=123456789;database=MyoaData;"
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="pd_mySelect"
objCmd.Commandtype=adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@wstr",adVarChar,adParamInput,200)
objCmd("@wstr")= wstr
set objRS=objCmd.Execute
NewBody 2004-12-07
如果想把一个数据列作为参数传给存储过程,该怎么写呢?
如1,3,4,8...(个数不定)
如1,3,4,8...(个数不定)
TigerSuper 2004-12-07
传一个字符串到存储过程里
然后自己分解一下,也不简单
然后自己分解一下,也不简单
SuperBJack 2004-12-07
有没有别的方法,像上面兄台这样,如果我有6个参数那不是很麻烦.
