Post reply

Dynamically determine DB and create stored proc??

  • December 29, 2004 at 5:13 am

    #86187

    Hi everyone

    I need to dynamically determine a database name and store it in a variable using T-SQL.

    Once I have the db name, I need to create a stored procedure in THAT database, however I receive the error message that the Create Statement has to be the first statement, so

    declare @sql varchar(2000)

    declare @nameOfDB varchar(50)

    set @nameOfDB = 'Monthly database'

    set @sql = 'use ' + @nameOfDB

    set @sql = @sql + ' CREATE PROCEDURE...'

    doesn't work. Adding the GO statement doesn't help either.

    Do you perhaps have a workaround for this problem?

    Thanks

     

     

     

  • December 29, 2004 at 5:17 am

    #535228

    See, if this helps:

    DECLARE @stmt NCHAR(1000)

    SET @stmt = 'USE Frank_3 '

     + CHAR(13) +

     'DECLARE @stmt NCHAR(1000)'

     + CHAR(13) +

     'SET @stmt = ''CREATE TABLE First (col_a int)'''

     + CHAR (13) +

     'PRINT (@stmt)'

    EXEC sp_executesql @stmt

    SET @stmt = 'USE Frank_3 '

     + CHAR(13) +

     'DECLARE @stmt NCHAR(1000)'

     + CHAR(13) +

     'SET @stmt = ''CREATE TRIGGER blahblah ON FIRST FOR INSERT AS SELECT * FROM FIRST'''

     + CHAR (13) +

     'EXEC (@stmt)'

    EXEC sp_executesql @stmt

    You might also want to read http://www.sommarskog.se/dynamic_sql.html

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2004 at 5:20 am

    #535229

    Forgot to mention, to determine the name of the DB the DB_NAME()  function might come in handy.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2004 at 5:47 am

    #535232

    Thanks. I have monthly DBs, so I use getdate() to determine the correct DB to create the stored proc in.

    I have tried it and although the query prints out correctly using the character, I still receive the following error message:

    Server: Msg 111, Level 15, State 1, Line 1

    'CREATE PROCEDURE' must be the first statement in a query batch.

    Thanks again for the help!

     

     

  • December 29, 2004 at 6:05 am

    #535234

    Can you post your statement?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 30, 2004 at 2:03 am

    #535376

    You need 'GO' + CHAR(13) in front of 'CREATE PROCEDURE ...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply