Query Dynamic SQL - SQL Server Forums

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)

Query Dynamic SQL

Author

Topic

lhonjo
Starting Member

1 Post

Posted - 2002-08-01 : 18:43:59

I'm trying to write a stored proc with conditional logic that takes two parameters (a SELECT statement and a category list).

Stored Procedure Requirements/Parameters:
@strQuery : required; any SELECT statement
@strCatList : optional; a list of categories to filter on

function : if a category list (@strCatList) is passed, the procedure will filter and return the results of @strQuery where the category is in @strCatList.

Typically I would use a temp or derived table. However, since the goal is to accept ANY select statement, the number and type of columns will change. And Execute(@strQuery) doesn't seem to work "inline."

This is an example "non-filtered" @strQuery recordset (potentially 500,000 records):
Description | Category
Striped Shirt | shirt
leather belt | accessories
Dotted Shirt | shirt
Polo Shirt | shirt
Denim Jeans | pants

This is what the resulting "filtered" recordset should look like if @strCatList is "accessories,pants" (potentially 150,000 records):
Description | Category
leather belt | accessories
Denim Jeans | pants

Here is a non-working example of what I am trying to accomplish in SQL Server 2000.
Create Procedure sp_CatFilter
(
@strQuery char(255) = '',
@strCatList char(255) = ''
)
As

/* declare local vars */
Declare @strSQL VarChar(1000)

SELECT @strSQL = @strQuery

/* if a category list was passed */
IF @strCatList <> ''
BEGIN
/* Execute filtered query */
SELECT *
FROM @strSQL
WHERE Category IN (@strCatList)
END

/* a division list was not passed */
ELSE
BEGIN
/* execute unfiltered query */
Exec ( @strSQL)
END
return

Any solutions out there?
Thanks!

Edited by - lhonjo on 08/01/2002 19:18:27

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-02 : 07:09:09

Something to watch out for
if they put in a go e.g.

'select * from tbl
go
delete tbl
'
that you don't execute this as dynamic sql.

Probably easiest to split the query into a select clause and where clause (including everything else). You will have to be careful when parsing that where has not been included as text in the select.

You can then add your filter at the begining of the where clause and execute the sql.

If you want to use a temp table you can use a select into command with openrowset to create the temp table.
You will then have to call another sp to do the filter because the format of the temp table just created will not be available to the creating sp (probably).
I don't recommend this.

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