Using a variable to form part of a table name in stored procedure

Question

Using a variable to form part of a table name in stored procedure

Rich Ray

SSC Eights!

Points: 887
More actions
November 29, 2008 at 2:27 pm

#132528

Hi all,
I have written a query to export some data in a specific format and that works but I want to put it into a stored procedure so the business unit can be parameterised. The table names are ZZZ_ACNT and ZZZ_ADDR where ZZZ is the business unit. I therefore need to make the table name @bus_unit + '_ACNT' for example but this does not work. I have read into table variables and have tried to use these but it still doesn't work. Any ideas would be much appreciated. I know I can use SSIS for this and maybe I will but I would like to know how to do it this way as well.
Thanks in advance,
Richard
CREATE PROCEDURE dbo.myProcedure
@bus_unit nvarchar(3)
AS
SELECT
@bus_unit + SPACE(7) + '|'
+((RTRIM(ADDR_CODE)) + SPACE(30-LEN(ADDR_CODE)))
+'|Creditor |'
+CASE
WHEN ADDR_LINE_1 IS NULL THEN SPACE(35) + '|'
WHEN ADDR_LINE_1 IS NOT NULL THEN ((RTRIM(ADDR_LINE_1)) + SPACE(35-LEN(ADDR_LINE_1)) + '|')
END
+CASE
WHEN ADDR_LINE_2 IS NULL THEN SPACE(35) + '|'
WHEN ADDR_LINE_2 IS NOT NULL THEN ((RTRIM(ADDR_LINE_2)) + SPACE(35-LEN(ADDR_LINE_2)) + '|')
END
+CASE
WHEN ADDR_LINE_3 IS NULL THEN SPACE(35) + '|'
WHEN ADDR_LINE_3 IS NOT NULL THEN ((RTRIM(ADDR_LINE_3)) + SPACE(35-LEN(ADDR_LINE_3)) + '|')
END
+CASE
WHEN ADDR_LINE_4 IS NULL THEN SPACE(35) + '|'
WHEN ADDR_LINE_4 IS NOT NULL THEN ((RTRIM(ADDR_LINE_4)) + SPACE(35-LEN(ADDR_LINE_4)) + '|')
END
+CASE
WHEN ADDR_LINE_5 IS NULL THEN SPACE(35) + '|'
WHEN ADDR_LINE_5 IS NOT NULL THEN ((RTRIM(ADDR_LINE_5)) + SPACE(35-LEN(ADDR_LINE_5)) + '|')
END
+CASE
WHEN STATE IS NULL THEN SPACE(35) + '|'
WHEN STATE IS NOT NULL THEN ((RTRIM(STATE)) + SPACE(35-LEN(STATE)) + '|')
END
FROM
(@bus_unit + '_ADDR') AD INNER JOIN
(@bus_unit + '_ACNT') ACC ON AD.ADDR_CODE = ACC.ACNT_CODE
WHERE ACC.ACNT_TYPE = 1
GO

Viewing 15 posts - 1 through 15 (of 22 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You should use dynamic SQL for this, unless you want to make one stored procedure for every business unit.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Either that, or a partitioned view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

rbarryyoung (11/29/2008)
You should use dynamic SQL for this, unless you want to make one stored procedure for every business unit.

If you choose to take the dynamic SQL route, then generally you will find short statements easier to work with (more likely to work first time) than longer ones:

SELECT@bus_unit + SPACE(7) + '|'

+ LEFT(ISNULL(ADDR_CODE,'') + SPACE(30),30)

+ '|Creditor |'

+LEFT(ISNULL(ADDR_LINE_1,'') + SPACE(35), 35) + '|'

+LEFT(ISNULL(ADDR_LINE_2,'') + SPACE(35), 35) + '|'

+LEFT(ISNULL(ADDR_LINE_3,'') + SPACE(35), 35) + '|'

+LEFT(ISNULL(ADDR_LINE_4,'') + SPACE(35), 35) + '|'

+LEFT(ISNULL(ADDR_LINE_5,'') + SPACE(35), 35) + '|'

+LEFT(ISNULL(STATE,'') + SPACE(35), 35) + '|'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

The cool part about your solution, Chris, is...

... it's not dynamic SQL... just good, clean, hard-coded concatenation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (11/29/2008)
Either that, or a partitioned view.

Heh. You know the first time I read this I thought that you said "Partitioned Table". I didn't realize until Chris's post that you actually said "Partitioned View". For the past day I've been thinking: "Is Jeff crazy? That won't work!" :w00t:

It's a good thing that we don't make mistakes. :Whistling:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rich Ray SSC Eights! Points: 887 More actions · Answer 6

Evening all,

Thanks for your suggestions.

However, its the table name that I need assistance on rather than the SELECT clause.

How would I do this dynamically?

Thanks!

Richard

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

OK, leaving aside the rest of the select statement, here is what you need to do with just the table names and the FROM clause for Dynamic SQL:

Declare @sql nvarchar(MAX)

--build the SQL statement

Select @sql = 'SELECT * FROM

(' + @bus_unit + '_ADDR) AD INNER JOIN

(' + @bus_unit + '_ACNT) ACC ON AD.ADDR_CODE = ACC.ACNT_CODE'

EXEC (@sql);

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rich Ray SSC Eights! Points: 887 More actions · Answer 8

Aha, I did see another thread actually where the query was actually a parameter - thanks very much!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

I should also mention that you need to insure that this does not become a SQL Injection target.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

rbarryyoung (11/30/2008)
Jeff Moden (11/29/2008)
Either that, or a partitioned view.
Heh. You know the first time I read this I thought that you said "Partitioned Table". I didn't realize until Chris's post that you actually said "Partitioned View". For the past day I've been thinking: "Is Jeff crazy? That won't work!" :w00t:
It's a good thing that we don't make mistakes. :Whistling:

Heh... you know the answer to that... yes, "Jeff is crazy". Tinfoil hat is proof enough, doncha think? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (11/30/2008)
Heh... you know the answer to that... yes, "Jeff is crazy". Tinfoil hat is proof enough, doncha think? 😉

Sorry, I can't hear you through the RF shielding in my helmet...

😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rich Ray SSC Eights! Points: 887 More actions · Answer 12

rbarryyoung (11/30/2008)
OK, leaving aside the rest of the select statement, here is what you need to do with just the table names and the FROM clause for Dynamic SQL:
Declare @sql nvarchar(MAX)
--build the SQL statement
Select @sql = 'SELECT * FROM
(' + @bus_unit + '_ADDR) AD INNER JOIN
(' + @bus_unit + '_ACNT) ACC ON AD.ADDR_CODE = ACC.ACNT_CODE'
EXEC (@sql);

OK, I tried this using the following......

CREATE PROCEDURE dbo.Sun5_OFAC_Export

@bus_unit varchar(3)

AS

Declare @sql nvarchar(MAX)

--build the SQL statement

Select @sql =

'SELECT ' + @bus_unit + ',

+ LEFT(ISNULL(ADDR_CODE,'') + SPACE(15),15)

+ ''|Creditor |''

+ LEFT(ISNULL(ADDR_LINE_1,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_2,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_3,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_4,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_5,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(STATE,'') + SPACE(35), 35) + '|'

FROM

('+ @bus_unit + '_ADDR) AD INNER JOIN

('+ @bus_unit + '_ACNT) ACC ON CONVERT(varchar(10),AD.ADDR_CODE) = CONVERT(varchar(10),ACC.ACNT_CODE)

WHERE ACC.ACNT_TYPE = 1

GO'

....but I get the following error:

Msg 402, Level 16, State 1, Procedure Sun5_OFAC_Export, Line 9

The data types varchar and varchar are incompatible in the boolean OR operator.

.....any ideas?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Hi Richard

Give this a try. When you've got it working, then's the time to put it into a stored procedure. Notice the PRINT statement? Take the output from this, paste it into QA (or SMS or whatever client you're using), and run it. There may still be one or two small changes to make.

[font="Courier New"]--CREATE PROCEDURE dbo.Sun5_OFAC_Export

--@bus_unit varchar(3)

--AS

DECLARE @sql NVARCHAR(1000), @bus_unit VARCHAR(3)

SET @bus_unit = 'BUS'

--build the SQL statement

SELECT @sql =

'SELECT ''' + @bus_unit + ''',

+ LEFT(ISNULL(ADDR_CODE,'''') + SPACE(15),15)

+ ''|Creditor |''

+ LEFT(ISNULL(ADDR_LINE_1,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_2,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_3,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_4,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_5,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(STATE,'''') + SPACE(35), 35) + ''|''

FROM

('+ @bus_unit + '_ADDR) AD INNER JOIN

('+ @bus_unit + '_ACNT) ACC ON CONVERT(varchar(10),AD.ADDR_CODE) = CONVERT(varchar(10),ACC.ACNT_CODE)

WHERE ACC.ACNT_TYPE = 1'

PRINT @sql

--GO

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rich Ray SSC Eights! Points: 887 More actions · Answer 14

Chris Morris (12/4/2008)
Give this a try. When you've got it working, then's the time to put it into a stored procedure. Notice the PRINT statement? Take the output from this, paste it into QA (or SMS or whatever client you're using), and run it. There may still be one or two small changes to make.

Thanks Chris. I have got the query working and have created an SP but need to know how to get the output to be displayed when the SP is executed. I need to pass the business unit in......

CREATE PROCEDURE dbo.Sun5_OFAC_Export

@bus_unit varchar(3)

AS

DECLARE @sql NVARCHAR(1000)

--, @bus_unit VARCHAR(3)

--SET @bus_unit = 'PK1'

--build the SQL statement

SELECT @sql =

'SELECT ''' + @bus_unit + '|' + '''

+ LEFT(ISNULL(ADDR_CODE,'''') + SPACE(15),15)

+ ''|Creditor |''

+ LEFT(ISNULL(ADDR_LINE_1,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_2,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_3,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_4,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(ADDR_LINE_5,'''') + SPACE(35), 35) + ''|''

+ LEFT(ISNULL(STATE,'''') + SPACE(35), 35) + ''|''

FROM

'+ @bus_unit + '_ADDR AD INNER JOIN

'+ @bus_unit + '_ACNT ACC ON CONVERT(varchar(10),AD.ADDR_CODE) = CONVERT(varchar(10),ACC.ACNT_CODE)

WHERE ACC.ACNT_TYPE = 1'

--PRINT @sql

EXEC @sql

GO

If I run EXEC Sun5_OFAC_Export BUS I get the following error:

Msg 203, Level 16, State 2, Procedure Sun5_OFAC_Export, Line 27

The name 'SELECT 'PK1|'

+ LEFT(ISNULL(ADDR_CODE,'') + SPACE(15),15)

+ '|Creditor |'

+ LEFT(ISNULL(ADDR_LINE_1,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_2,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_3,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_4,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(ADDR_LINE_5,'') + SPACE(35), 35) + '|'

+ LEFT(ISNULL(STATE,'') + SPACE(35), 35) + '|'

FROM

PK1_ADDR AD INNER JOIN

PK1_ACNT ACC ON CONVERT(varchar(10),AD.ADDR_CODE) = CONVERT(varchar(10),ACC.ACNT' is not a valid identifier.

....must be something simple I know but just need this last bit of help 🙂

Thanks!