conditional WHERE clause

Hi
I have an SP with a few params and I wish to use one of the params in the
WHERE clause, to conditionaly set a WHERE statement, for example:
CREATE PROC sp_Test
@.ProductCode VARCHAR(5)
@.ProcessMonth INT = NULL,
@.ProcessYear INT = NULL
AS
SELECT Table.ID
FROM Table
WHERE
Table.PMonth = @.ProcessMonth
AND Table.PYear = @.ProcessYear
AND
IF @.ProductCode = 'ALL' THEN
BEGIN
Table.ProductCode = ('A','B','C' etc...all codes, need this to
be dynamic)
END
ELSE
BEGIN
Table.ProductCode = @.ProductCode
END
Hope this makes sense, I also don't know how to make the 'ALL' return all
records?
Kind Regards
RickyPWHERE
ProductCode = CASE @.ProductCode WHEN 'ALL' THEN ProductCode ELSE
@.ProductCode END
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||You may benefit by reading the article at:
http://www.sommarskog.se/dyn-search.html
Anith|||Hi
use northwind
go
create proc spproc
@.custid varchar(10)
as
select * from orders where CustomerID=
case when @.custid='all' then CustomerID else @.custid end
--usage
exec spproc 'vinet'
exec spproc 'all'
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||Hi Ricky,
How about :
SELECT * FROM MyTable
WHERE (Table.ProductCode = @.ProductCode OR @.ProductCode = 'ALL')
Should do what you're looking for. Personally, rather than 'ALL' I'd use
NULL to indicate that no filter should be applied (i.e. all product codes),
but this will work fine.
Cheers,
Alex
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||Great minds think alike - thanks guys for the postings, didn't know you
could do that.
Kind Regards
RickyP
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>

Conditional input params to SP

I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?

Dim strWhere
strWhere = " WHERE dbo.""User"".UID IS NOT NULL "

If Not request.querystring("EmployerID") = "" Then
strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'"
End If

If Not request.querystring("AccountNumber") = "" Then
strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'"
End If

If Not request.querystring("LastName") = "" Then
strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'"
End If

If Not request.querystring("FirstName") = "" Then
strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'"
End If

DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr"))
DBCommand = New OleDbDataAdapter _
("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)

Thanks in advance?Here is a pretty complete, though somewhat dated, overview on doing what you are looking to do (in this case inside a stored procedure, but you could do the same with a parameterized query, I expect).

What you are doing is succeptable to SQL Injection attacks, and a number of other potential problems...|||Thanks much. Great article, just what I was looking for.

I'll be coding with SQL Injection in mind as well...|||example of how i go about optional inputs

create procedure myprocedure
@.field1 type = null,
@.field2 type = null,
@.field3 type = null

as

select * from mytable where field1 = isnull(@.field1, field1)
and field2 = isnull(@.field2, field2)
and field3 = isnull(@.field3, field3)

this could be taxing on a table with a lot of entries, but for small tables this works out well and keeps me from having to write multiple sprocs for different parms