写一个带参数的存储过程??

[SuperBJack]

只有一个存储过程"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

_____________________--

[SuperBJack]

有没有别的方法,像上面兄台这样,如果我有6个参数那不是很麻烦.

[TigerSuper]

传一个字符串到存储过程里
然后自己分解一下,也不简单

[NewBody]

如果想把一个数据列作为参数传给存储过程,该怎么写呢?

如1,3,4,8...(个数不定)

[SuperBJack]

问题解决:
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

[lienzhu]

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

[sywcf]

收藏