CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4324

Adding database and schema for a variable

I wanted to ask how to print the database and schema name of a table for the '@tablename' variable? It's because the update is simply just saying the table name like 'Baggage' and Baggage could be in either 'DB1.dbo.Baggage' or 'DB2.dbo.Baggage', so I want the correct database and schema to be retrieved.

    select @tablename = ''
        select @tablename = Value

        print 'update ' + @tablename + '
                        set ' + @update + '
                        ' + @whereClause  +  isnull(@addnlClause, '') + '
                        if @@ROWCOUNT <> 1 
                            BEGIN
                                THROW 99999, ''Error - Single Row not updated, rollback terminated'', 1;
                            END'
                        set nocount off

UPDATE:

Ok so lets say I have a database called 'SupportAudit' which contains all the procs. I have a database called 'Holidays' which contains its own tables, and another database called 'Reservations' containing its own tables. Below are examples of the tables these databases could have:

Holidays

dbo.Package

dbo.Product

Search.FlightList

Support.DataInputList

Reservations

dbo.Package

dbo.ProductPerson

Search.ProviderList

Support.DateRange

Now usually in our procs, when we refer to these tables, we refer to them as [database].[schema].[tablename]. e.g. [Holidays].[dbo].[Package] or [Reservations].[Support].[DateRange].

Now in the update which is only a print, we are trying to perform a rollback to our last update, so we want the print to reference to the exact table we want to update, to do this we need the exact table by using the [database].[schema].[tablename] method.

Hopefully that makes sense.

Upvotes: 0

Views: 75

Answers (1)

eftpotrm
eftpotrm

Reputation: 2271

You probably want to use DB_NAME() something like this - 

SET @tablename = DB_NAME() + '.dbo.' + Value

But, I really wouldn't do what you look like you're trying; an 'update any table' routine is one of those classic anti-patterns in SQL. Bad performance over time, harder to keep track of what's doing what and keep it in sync with other changes. Update sprocs for each distinct job really are a better way to do it.

Upvotes: 2

Related Questions