[thelist] SProcs - SELECT queries
Chris at globet.com
Chris at globet.com
Thu Jun 23 09:30:43 CDT 2005
All
I would like to produce a single stored procudure in SQL Server 2000
that takes the place of several inline SQL queries.
The queries are as follows:
SELECT aField,
bField,
cField,
dField
FROM aTable
WHERE aField = 5
SELECT aField,
bField,
cField,
dField
FROM aTable
WHERE bField = 'qwe'
AND cField = 6
SELECT aField,
bField,
cField,
dField,
FROM aTable
WHERE dField = 'rty'
AND aField = 7
If I were to use ASP to build the inline query, I would perhaps try
something along the following lines:
<%
Dim strSQL
strSQL = strSQL & "SELECT aField,"
strSQL = strSQL & " bField,"
strSQL = strSQL & " cField,"
strSQL = strSQL & " dField"
strSQL = strSQL & " WHERE 1 = 1"
If (bCondition1) Then strSQL = strSQL & " AND aField = " & aValue
If (bCondition2) Then strSQL = strSQL & " AND bField = " & bValue
If (bCondition3) Then strSQL = strSQL & " AND cField = " & cValue
If (bCondition4) Then strSQL = strSQL & " AND dField = " & dValue
Set oRS = oConn.Execute(strSQL)
%>
1) How would I go about so structuring a stored procedure with optional
parameters that I could then use in different places for different
queries?
2) Is this possible?
3) If so, is it a good idea?
Many thanks in advance!
Regards
Chris Marsh
More information about the thelist
mailing list