[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