Creating stored procedure in another database
Solution 1
It's a pain, but this is what I do. I took this from an example I found on sqlteam, I think - you might have some quoting issues 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)
Solution 2
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)
John
Updated on June 06, 2022Comments
-
John about 2 years
Any idea if it's possible to create a procedure in another database using T-SQL alone, where the name of the database is not known up front and has to be read from a table? Kind of like this example:
Use [MasterDatabase] Declare @FirstDatabase nvarchar(100) Select Top 1 @FirstDatabase=[ChildDatabase] From [ChildDatabases] Declare @SQL nvarchar(4000) Declare @CRLF nvarchar(10) Set @CRLF=nchar(13)+nchar(10) Set @SQL = 'Use [+'@Firstdatabase+']'+@CRLF+ 'Go'+@CRLF+ 'Create Proc [Test] As Select 123' Exec (@SQL)
See what I'm trying to do? This example fails because Go is actually not a T-SQL command but it something recognised by the query analyser/SQL management studio and produces an error. Remove the Go and it also fails because Create Proc must be the first line of the script. Arrgg!!
The syntax of T-SQL doesn't allow you do things like this:
Create [OtherDatabase].[dbo].[Test]
Which is a shame as it would work a treat! You can do that with Select statements, shame it's inconsistent:
Select * From [OtherDatabase]..[TheTable]
Cheers, Rob.
-
John over 15 yearsHmm, yes I can see that working. An Exec within and Exec. Boy will the quoting get hairy!