Subquery as part of the search condition -- sql server programming

a8ap NO[at]SPAM yahoo.com
3/11/2005 11:56:38 PM

Hello All,

I just started in developing a web based report application with SQL
2000 reporting services. While most data filtering requirements can be
handled by simple parameters, there are some occasional special
requirements. For simplicity, i have the idea of creating a table with
"Filter Name" and "Filter Expression". The Filter expression stores
the logical SQL expressions like "field1 = '...' and .... or ...". At
the report, the user pick up standard parameters and optionally a
special filter, and the logical expression will be retrieved and used
as part of the SQL statement.

Now, the question is what is the best method of concatenating a
logical expression retrieved via subquery into the main SQL statement
for final output.

Method 1:
Select * from TableA where .... and (Select FilterExpression from ...)

Method 2:
Set @Temp = (Select FilterExpression from ...)
Exec(Select * from TableA where .... and @Temp)

Method 1 certainly don't work, i really hope to hear that it can work
with some function or operator

Method 2 works but i believe there is some inefficiency, and also it
seems the Visual Studio designer for reporting services don't like the
Exec().

Kathi Kellenberger
3/12/2005 3:45:02 AM

One way to solve this would be to create a stored procedure that accepts as
parameters the filter expression. Reporting services does work well with
stored procs. In your stored proc, you would build the select statment and
execute it using dynamic SQL -- use the EXEC statement in the stored proc.

Example*:

create proc uspMyProc @FilterExpression varchar(1000) as
declare @SQLStatement varchar(1000)
set @SQLStatement = 'select * from tableA where ' + @FilterExpression
exec @SQLStatement
return

*Not debugged.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Subquery as part of the search condition