Passing Database name as parameter in a stored procedure

  • The present database server I have consists of 6 databases based on Technical & Non technical sectors.

    For Technical sector, has 3 DBs namely BizTech, BizTechArchive & BizTechLog

    For Non technical sector, has 3 DBs namely BizNonTech, BizNonTechArchive & BizNonTechLog.

    BizTech & BizNonTech are the main databases. BizTechArchive & BizNonTechArchive arethe archive Dbs where data is archived when some updation & deletion happens in the main DBs. These archive DBs has all tables present in the main DBs.  The other DBs are for logging the error if some error occurs.

    For example, in every stored proc of BizTech database, in the CATCH block, error is caught & inserted into respective Log DB tables.

    INSERT INTO BizTechLog.dbo.errorlog (Collumn1, Column2..)

    How can I pass the database name as a parameter such that I can pass it to every stored procedure in BizNonTech CATCH block i.e. BizNonTech is passed as input param say @dbname & used as

    INSERT INTO @dbname.dbo.errorlog (Collumn1, Column2..)

    Please guide me on this. Thanks

     

     

     

     

  • no need to pass anything as a parameter.

    In each database just setup a synonym pointing to the corresponding log db and on your code you just use the synonym in instead of the hardcoded db name (which should almost never be used (possible exception for utilities databases)

  • Dear friend, thanks for the insight. You made my day.

    I have created Synonyms & it is working.

    Now I have a doubt. I have created a Synonym for 'BizTechArchive. dbo' in main DB BizTech

    USE BizTech
    CREATE SYNONYM Int_Archive FOR BizTechArchive.dbo;

    But when I execute the below command in BizTech DB, it is throwing error. Why is it so?

    select * from Int_Archive.Table1

    Should I create a seperate SYNONYM for BizTechArchive.dbo.Table1 and all other tables in BizTechArchive DB?

     

  • Unfortunately, you can't create a synonym for a schema, only for individual objects.  The reason you didn't get an error when you created the synonym is that the name is checked at run time, not at create time.

    John

  • Even though I can create a synonym for "BizTechArchive.dbo", I cannot use it.

    So I need to create individual synonyms for each table like BizTechArchive.dbo.Table1

    Is there any alternative for this?

     

  • Yes.  You can use dynamic SQL.  Make sure you understand what SQL injection is and how to prevent it before you play with this.

    DECLARE @DBName sysname;
    DECLARE @SQL nvarchar(200);

    -- This step is essential to guard against SQL injection
    SELECT @DBName = name
    FROM sys.databases
    WHERE name = @DBName;

    SET @SQL = N'INSERT INTO ' + @DBName;
    SET @SQL = @SQL + N'.dbo.errorlog (Collumn1, Column2..)';

    EXEC sys.sp_executesql @stmt = @SQL;

    John

  • Hey George,

    This gets into a rather tricky area. Referencing error message 15250:

    "The database name component of the object qualifier must be the name of the current database."

    As John suggested, this will likely require dynamic SQL. There's a great question on StackOverflow in which the OP wants to return the remote database name.

    How to return the remote database name which is calling a stored proc in a different database?

    I have accomplished this before using dynamic SQL and embedding the following code within my stored procedure:

    IF @DatabaseName IS NULL
    BEGIN
    SELECT TOP (1) @DatabaseName = DB_NAME(resource_database_id)
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@SPID
    AND resource_type = 'DATABASE'
    AND request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'
    ORDER BY CASE WHEN resource_database_id = DB_ID() THEN 1 ELSE 0 END;
    END;

    I hope this helps to at least point you in the right direction!

    • This reply was modified 5 years, 2 months ago by  BTylerWhite.

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

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