Creating stored procedure in another database

Question

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.

Answer 1

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