Dynamic WHERE in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic WHERE in stored procedure

Author

Topic

charford
Starting Member

8 Posts

Posted - 2007-09-29 : 11:01:20

Can anyone help me with this dumb question?
I want to use a stored procedure to bring back a recordset depending if a bit column is set to 1. My table has a number of columns that are of Data Type bit and I want to be able to specify which particular column I'm interested in as a parameter when I call the Stored Procedure.

I have set up the Stored Procedure as follows:

CREATE PROCEDURE getProducts
@param1 varchar(50)
AS
SELECT ProductID, ProductName
FROM dbo.Products
WHERE @param1 = '1'
GO

I'm calling it like this:

Dim cmdX, cmdParam, rsX
cmdParam = "OnSpecial"

set cmdX = Server.CreateObject("ADODB.Command")
cmdX.ActiveConnection = conn_STRING
cmdX.CommandText = "dbo.getProducts"
cmdX.Parameters.Append cmdX.CreateParameter("@RETURN_VALUE", 3, 4)
cmdX.Parameters.Append cmdX.CreateParameter("@param1", 200, 1,50,cmdParam)
cmdX.CommandType = 4
cmdX.CommandTimeout = 0
cmdX.Prepared = true
set rsX = cmdX.Execute
rsX_numRows = 0

I know for a fact that I have products in my dbase with the bit column 'OnSpecial' set to 1, yet no records are coming back.

Any pointers would be most appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-29 : 13:36:22

That will return data for @param = '1'
It's never '1' but is a column name
try
exec 'SELECT ProductID, ProductName
FROM dbo.Products
WHERE ' + @param1 + ' = 1'

But it will need permission on the table and is a security risk.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

charford
Starting Member

8 Posts

Posted - 2007-09-29 : 18:56:57

Interesting idea
I have never used exec in a SP.

In the end I gave up trying to make the column name dynamic and went for fixed options like this:

WHERE
CASE @param1
WHEN 'OnSpecial' THEN OnSpecial
WHEN 'New' THEN New
WHEN 'BestSeller' THEN BestSeller
END
= '1'

This works but means if I ever add another option I will have to edit the SP.
I'll try your exec' suggestion and see what happens.

many thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-29 : 19:42:42

If you include them all then you will only have to change the SP when you change the table structure. Which highlights the underlying issue with the design.
If you have another table with the 1 - many attribute you don't have a problem.
There would be a lookup table with the descriptions and a conjoint table for the product attributes.
so the query would become
select p.ProductID, p.ProductName
from Products p
join ProductAttributes pa
on p.ProductID = pa.ProductID
where pa.AttrubuteID = (select AttrubuteID from Attributes where description = @param1)

You could also use the Attributes table to get a criteri list for a front end.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources