January 12, 2016 at 9:15 am
I am trying to execute the following script but need to pass two parameters, I'm not sure how too, I know the script works with a stored procedure not requiring a parameter.
Declare @bdate as date ='2015-01-01'
Declare @edate as date = '2015-12-31'
DECLARE @sql nvarchar(4000);
DECLARE @BiSQL nvarchar(4000);
DECLARE @dName varchar(100);
SET @dName = 'XX'; SET @sql = 'usp_ME_Test';
SET @BiSQL = 'USE ' + @dName + '; EXEC sp_executesql N''' + @sql + ''''; EXEC (@BiSQL)
January 12, 2016 at 9:45 am
Review Books Online for sp_executesql. BOL is a GREAT resource!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 12, 2016 at 9:59 am
Could you post the actual SQL you are trying to generate. I'm a little lost from the what you posted and knowing what the final result that would be executed would really help.
January 12, 2016 at 10:04 am
decreale (1/12/2016)
I am trying to execute the following script but need to pass two parameters, I'm not sure how too, I know the script works with a stored procedure not requiring a parameter.Declare @bdate as date ='2015-01-01'
Declare @edate as date = '2015-12-31'
DECLARE @sql nvarchar(4000);
DECLARE @BiSQL nvarchar(4000);
DECLARE @dName varchar(100);
SET @dName = 'XX'; SET @sql = 'usp_ME_Test';
SET @BiSQL = 'USE ' + @dName + '; EXEC sp_executesql N''' + @sql + ''''; EXEC (@BiSQL)
Piling on the already supplied fine answers
😎
Declare @bdate as date ='2015-01-01'
Declare @edate as date = '2015-12-31'
DECLARE @sql nvarchar(MAX);
DECLARE @BiSQL nvarchar(MAX);
DECLARE @dName SYSNAME;
DECLARE @PARAM_STRING NVARCHAR(MAX) = N'@bdate DATE, @edate DATE';
SET @dName = 'XX'; SET @sql = 'usp_ME_Test';
SET @BiSQL = 'USE ' + @dName + '; EXEC' + @sql;
EXEC sp_executesql @BiSQL,@PARAM_STRING,@bdate,@edate;
January 12, 2016 at 10:05 am
NA
January 12, 2016 at 10:06 am
It's funny how you're trying to call a stored procedure using dynamic sql. You're basically being redundant making nested calls. I'm assuming this is to call the same procedure in different databases. If you're going further trying to make an uber procedure to handle all, just don't. It's not worth the trouble. If it's just to call the procedure in different databases, why don't you just adjust the connection properties?
Anyway, here's a way to do it, just showing how redundant it looks.
Declare @bdate as date ='2015-01-01'
Declare @edate as date = '2015-12-31'
DECLARE @sql nvarchar(128);
DECLARE @BiSQL nvarchar(4000);
DECLARE @dName varchar(128);
SET @dName = 'XX';
SET @sql = 'usp_ME_Test';
SET @BiSQL = 'EXEC sp_executesql N''EXEC ' + QUOTENAME( @dName) + '..' /*This should really have a schema*/+ QUOTENAME( @sql) + ' @bdate, @edate'', '
+ 'N''@bdate date, @edate date'', @bdate, @edate';
PRINT @BiSQL;
EXEC sp_executesql @BiSQL, N'@bdate date, @edate date', @bdate, @edate;
January 12, 2016 at 10:24 am
If you need a procedure to run in the context of multiple databases, create it in the master db, start the proc name with "sp_", and, after you create it, mark it as a system procedure. Then you can use it from any db.
EXEC db1.dbo.sp_uber_proc <params>
EXEC db2.dbo.sp_uber_proc <params>
EXEC db3.dbo.sp_uber_proc <params>
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 12, 2016 at 10:26 am
Thank You PERFECT!!!
January 12, 2016 at 11:46 am
Luis Cazares (1/12/2016)
It's funny how you're trying to call a stored procedure using dynamic sql. You're basically being redundant making nested calls. I'm assuming this is to call the same procedure in different databases. If you're going further trying to make an uber procedure to handle all, just don't. It's not worth the trouble. If it's just to call the procedure in different databases, why don't you just adjust the connection properties?Anyway, here's a way to do it, just showing how redundant it looks.
Declare @bdate as date ='2015-01-01'
Declare @edate as date = '2015-12-31'
DECLARE @sql nvarchar(128);
DECLARE @BiSQL nvarchar(4000);
DECLARE @dName varchar(128);
SET @dName = 'XX';
SET @sql = 'usp_ME_Test';
SET @BiSQL = 'EXEC sp_executesql N''EXEC ' + QUOTENAME( @dName) + '..' /*This should really have a schema*/+ QUOTENAME( @sql) + ' @bdate, @edate'', '
+ 'N''@bdate date, @edate date'', @bdate, @edate';
PRINT @BiSQL;
EXEC sp_executesql @BiSQL, N'@bdate date, @edate date', @bdate, @edate;
January 12, 2016 at 11:52 am
Luis Cazares (1/12/2016)
It's funny how you're trying to call a stored procedure using dynamic sql. You're basically being redundant making nested calls. I'm assuming this is to call the same procedure in different databases. If you're going further trying to make an uber procedure to handle all, just don't. It's not worth the trouble. If it's just to call the procedure in different databases, why don't you just adjust the connection properties?Anyway, here's a way to do it, just showing how redundant it looks.
Declare @bdate as date ='2015-01-01'
Declare @edate as date = '2015-12-31'
DECLARE @sql nvarchar(128);
DECLARE @BiSQL nvarchar(4000);
DECLARE @dName varchar(128);
SET @dName = 'XX';
SET @sql = 'usp_ME_Test';
SET @BiSQL = 'EXEC sp_executesql N''EXEC ' + QUOTENAME( @dName) + '..' /*This should really have a schema*/+ QUOTENAME( @sql) + ' @bdate, @edate'', '
+ 'N''@bdate date, @edate date'', @bdate, @edate';
PRINT @BiSQL;
EXEC sp_executesql @BiSQL, N'@bdate date, @edate date', @bdate, @edate;
You mentioned change the connection properties? This stored procedure is being used in SSRS. I wanted to create one report and have the user pass the database parameter to any of the 5 companies. I just have one report to modify and not 5
Thank you
January 12, 2016 at 12:22 pm
decreale (1/12/2016)
You mentioned change the connection properties? This stored procedure is being used in SSRS. I wanted to create one report and have the user pass the database parameter to any of the 5 companies. I just have one report to modify and not 5
Thank you
Basically, you need to define your Connection String in the Data Source as an Expression. That way, you can use your database parameter as part of the connection when calling the report. No need for dynamic sql whatsoever.
The expression might look like this:
="Data Source=MyServerName;Initial Catalog="&Parameters!Database.Value
Be sure that you're not using this connection for anything that would depend on the value of your parameter when it's not set yet.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply