CODEWITHSUNDEEP
sql-server
Harry Valters
Harry Valters

Reputation: 1

SQL use a variable as TABLE NAME in a FROM

We install our database(s) to different customers and the name can change depending on the deployment. What I need to know is if you can use a variable as a table name. The database we are in is ****_x and we need to access ****_m. This code is part of a function. I need the @metadb variable to be the table name - Maybe using dynamic SQL with sp_executesql. I am just learning so take it easy on me.

CREATE FUNCTION [dbo].[datAddSp] (
@cal NCHAR(30) -- calendar to use to non-working days
,@bDays INT -- number of business days to add or subtract
,@d DATETIME 
    )

RETURNS DATETIME
AS
BEGIN
DECLARE @nDate DATETIME -- the working date
,@addsub INT -- factor for adding or subtracting
,@metadb sysname

SET @metadb = db_name()
SET @metadb = REPLACE (@metadb,'_x','_m')
SET @metadb = CONCAT (@metadb,'.dbo.md_calendar_day')
SET @ndate = @d

 IF @bdays > 0
SET @addsub = 1
ELSE
SET @addsub = -1

IF @cal = '                              ' OR @cal IS NULL
SET @cal = 'CA_ON'
WHILE @bdays <> 0 -- Keep adding/subtracting a day until @bdays becomes 0
BEGIN
SELECT @ndate = dateadd(day, 1 * @addsub, @ndate) -- increase or decrease    @ndate

SELECT @bdays = CASE 
WHEN (@@datefirst + datepart(weekday, @ndate)) % 7 IN (0, 1) -- ignore if it is Sat or Sunday 
THEN @bdays
WHEN ( SELECT 1
FROM @metadb -- **THIS IS WHAT I NEED** (same for below) this table holds the holidays
WHERE mast_trunkibis_m.dbo.md_calendar_day.calendar_code = @cal AND  mast_trunkibis_m.dbo.md_calendar_day.calendar_date = @nDate AND mast_trunkibis_m.dbo.md_calendar_day.is_work_day = 0
 ) IS NOT NULL -- ignore if it is in the holiday table
 THEN @bdays
 ELSE @bdays - 1 * @addsub -- incr or decr @ndate
 END
 END
 RETURN @nDate
 END
 GO

Upvotes: 0

Views: 118

Answers (1)

gmiley
gmiley

Reputation: 6604

The best way to do this, if you aren't stuck with existing structures is to keep all of the table structures and names the same, simply create a schema for each customer and build out the tables in the schema. For example, if you have the companies: Global Trucking and Super Store you would create a schema for each of those companies: GlobalTrucking and SuperStore are now your schemas.

Supposing you have products and payments tables for a quick example. You would create those tables in each schema so you end up with something that looks like this:

GlobalTrucking.products
GlobalTrucking.payments

and

SuperStore.products
SuperStore.payments

Then in the application layer, you specify the default schema name to use in the connection string for queries using that connection. The web site or application for Global Trucking has the schema set to GlobalTrucking and any query like: SELECT * FROM products; would actually automatically be SELECT * FROM GlobalTrucking.products; when executed using that connection.

This way you always know where to look in your tables, and each customer is in their own segregated space, with the proper user permissions they will never be able to accidentally access another customers data, and everything is just easier to navigate.

Here is a sample of what your schema/user/table creation script would look like (this may not be 100% correct, I just pecked this out for a quick example, and I should mention that this is the Oracle way, but SQL Server should be similar):

CREATE USER &SCHEMA_NAME IDENTIFIED BY temppasswd1;
CREATE SCHEMA AUTHORIZATION &SCHEMA_NAME
CREATE TABLE "&SCHEMA_NAME".products
(
    ProductId           NUMBER,
    Description         VARCHAR2(50),
    Price               NUMBER(10, 2),
    NumberInStock       NUMBER,
    Enabled             VARCHAR2(1)
)
CREATE TABLE "&SCHEMA_NAME".payments
(
    PaymentId           NUMBER,
    Amount              NUMBER(10, 2),
    CardType            VARCHAR2(2),
    CardNumber          VARCHAR2(15),
    CardExpire          DATE,
    PaymentTimeStamp    TIMESTAMP,
    ApprovalCode        VARCHAR2(25)
)
GRANT SELECT ON "&SCHEMA_NAME".products TO &SCHEMA_NAME
GRANT SELECT ON "&SCHEMA_NAME".payments TO &SCHEMA_NAME
;

However, with something like the above, you only have 1 script that you need to keep updated for automation of adding new customers. When you run this, the &SCHEMA_NAME variable will be populated with whatever you choose for the new customer's username/schemaname, and an identical table structure is created every time.

Upvotes: 1

Related Questions