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.
| Author |
Topic |
|
jcartaxo
Starting Member
3 Posts |
 Posted - 2009-05-29 : 09:36:26
|
| Is it possible to use expressions in calling a stored procedure in a dataset?For example, I want to use a parameter to determine which stored procedure to call to load a dataset; something like this:iif(Parameters!Test.Value = "X", schemaA.my_pkg.getData, schemaB.my_pkg.getData)This, obviously, does not work. Is there another way to do this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-29 : 15:09:07
|
| why do you want to call stored procedure conditionally from dataset? isnt it better to create two datasets based on each and them use fields from them based on your condition ? |
 |
|
|
jcartaxo
Starting Member
3 Posts |
Posted - 2009-06-03 : 08:01:42
|
| visakh16,I want the report to work in multiple schemas without having two versions and changing the schema name.Thanks for the suggestion but if I created multiple datasets like you suggest I would have to have conditional logic everywhere I use the data. This seems like a lot of trouble with all the data elements I'm using. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-03 : 13:55:42
|
| you dont need a conditional logic for this.your report will work fine as long as you're not specifically using schema name in query. based on what default schema of user is who is running the report, it will pick up the correct procedure, execute and return data. |
|
|
|
jcartaxo
Starting Member
3 Posts |
Posted - 2009-06-17 : 08:07:59
|
| The trouble is the report is not always run against the default schema so I do need to specify the schema name.Using aliases could be a work-around but the DBA here hasn’t been agreeable to that. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-17 : 08:15:41
|
| I guess you could change the stored proc to reference a SYNONYM and then alter the SYNONYM before the stored proc call based on your variables?Assuming you are using 2005 and better.It's still a horrible dirty hack though.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
expressions to call stored procedure?