[ACCEPTED]-Creating stored procedure in another database-stored-procedures
It's a pain, but this is what I do. I took 3 this from an example I found on sqlteam, I 2 think - you might have some quoting issues 1 with the way I did the indiscriminate REPLACE:
DECLARE @sql AS varchar(MAX)
DECLARE @metasql as varchar(MAX)
DECLARE @PrintQuery AS bit
DECLARE @ExecQuery AS bit
SET @PrintQuery = 1
SET @ExecQuery = 0
SET @sql =
'
CREATE PROCEDURE etc.
AS
BEGIN
END
'
SET @metasql = '
USE OtherDatabase
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
'
IF @PrintQuery = 1
PRINT @metasql
IF @ExecQuery = 1
EXEC (@metasql)
DECLARE @UseAndExecStatment nvarchar(4000),
@SQLString nvarchar(4000)
SET @UseAndExecStatment = 'use ' + @DBName +' exec sp_executesql @SQLString'
SET @SQLString = N'CREATE Procedure [Test] As Select 123'
EXEC sp_executesql @UseAndExecStatment,
N'@SQLString nvarchar(4000)', @SQLString=@SQLString
0
This is how i have done with Alter Procedure:
DECLARE @metasql as varchar(MAX)
DECLARE @sql AS varchar(MAX)
SET @sql =
'ALTER PROCEDURE [dbo].[GetVersion]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP(1)[Version] from VersionTable
END'
SET @metasql = '
USE MyProdDb
IF (OBJECT_ID(''GetVersion'') IS NOT NULL OR OBJECT_ID(''GetVersion'', ''P'') IS NOT NULL)
BEGIN
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
END
'
--PRINT @metasql
EXEC (@metasql)
0
You could shell out to osql using xp_cmdshell, I 1 suppose.
I dont think this can be done with TSQL.
You 9 could use an SSIS package that looped the 8 names and connected to the servers dynamically 7 which creates the schema (procs ) you need.
This 6 is probably what I would do as it means 5 it is all contained within the package.
Configuration 4 can be kept separate by either using a table 3 or external xml file that contained the 2 list of server/databases to deploy the schema 1 to.
It's not necessary to use EXEC within EXEC.
You can simply use OtherDatabase.sys.sp_executesql
DECLARE @sql AS varchar(MAX) = N'
CREATE PROCEDURE etc.
AS
BEGIN
-- whatever
END
';
PRINT @sql;
EXEC OtherDatabase.sys.sp_executesql @sql;
0
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.