Where clause as a parameter - 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 2005 Forums

Analysis Server and Reporting Services (2005)

Where clause as a parameter

Author

Topic

ajhuddy
Starting Member

3 Posts

Posted - 2007-08-22 : 17:04:32

I have posted this question in the MSDN SSRS forum, but have had no luck. I'm hoping someone can give me a definite yes or no if this is possible...

I want to be able to define a parameter for a report that will represent the entire where clause.

E.g. the report's dataset will be:
SELECT name, phone FROM person WHERE @whereclause

Has anyone accomplished this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 17:24:59

You would need to use dynamic SQL for that.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ajhuddy
Starting Member

3 Posts

Posted - 2007-08-22 : 18:34:29

quote:
Originally posted by dinakar

You would need to use dynamic SQL for that.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Can you please provide me some sort of proof-of-concept?

I have tried something like the following, but it does not work:

DECLARE @clause AS nvarchar(1000)
SET @clause = "SELECT name, phone FROM person "
SET @clause = @clause + @report_parameter
EXEC sp_executesql @clause

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 19:22:12

DECLARE @clause AS nvarchar(1000)
SET @clause = "SELECT name, phone FROM person "
SET @report_parameter = 'WHERE somecolumn = somecondition'
SET @clause = @clause + @report_parameter
EXEC sp_executesql @clause

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 19:22:41

Also read up books on line for sp_ExecuteSQL. They have some cample code too that you can build yout code off of.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources