Constructing SQL Query on the fly in a 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

SQL Server 2000 Forums

SQL Server Development (2000)

Constructing SQL Query on the fly in a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-29 : 12:12:03

Peter writes "I want to construct an SQL query in a store proc dependant on which criteria
are passed in. This is easy to do on my ASP page, but I want to move all SQL
code from the ASP into a store proc to make it easier to maintain.

For example, I have a query which will pull back property information
and depening on the users choice, it can pull back all properties in
a particular area, or in all areas

Here's a simplified version of the code I've tried:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE PROCEDURE spMyProc
@idUser int,
@idCounty int,
@idDistrict int
AS
BEGIN

SELECT p.Address
FROM Property p

WHERE p.idCreatedBy = @idUser
IF NOT @idCounty = 0
AND p.idCounty = @idCounty
IF NOT @idDistrict = 0
AND p.idDistrict = @idDistrict

END
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

In the code, I'm trying to construct SQL on the fly
depening on whether or not idCounty and idDistrict are 0
(if they are 0, then I don't want them as part of my WHERE statement,
and instead want to return all areas/districts)

Is this even possible under stored procs?

This throws loads of errors since the syntax is wrong
but can you translate this into the correct syntax?

Thanks"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-29 : 12:20:55

where (user = @user or @user = 0)
and (County = @County or @COunty = 0)
and ...

==========================================
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