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

Question

Post reply

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

Viewing 8 posts - 16 through 22 (of 22 total)

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

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

Hi Richard

The statement is longer than NVARCHAR(1000), so increase the value:

DECLARE @sql NVARCHAR(2000) should be more than enough.

^{“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 2

I increased it to 4000 and it still doesn't work. Slightly amended the query as well taking out the CONVERT's...

ALTER PROCEDURE dbo.Sun5_OFAC_Export

@bus_unit varchar(3)

AS

DECLARE @sql NVARCHAR(4000)

--, @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 AD.ADDR_CODE = ACC.ACNT_CODE

WHERE ACC.ACNT_TYPE = 1'

--PRINT @sql

EXEC @sql

GO

Same error though.......?

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

Richard, can you post the result of the print statement please?

^{“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 4

Chris Morris (12/4/2008)
Richard, can you post the result of the print statement please?

Sure.....

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 AD.ADDR_CODE = ACC.ACNT_CODE

WHERE ACC.ACNT_TYPE = 1

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

This works for me...just a couple of small changes

ALTER PROCEDURE dbo.Sun5_OFAC_Export

@bus_unit varchar(3)

AS

Declare @sql varchar(2000)

--build the SQL statement

SET @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'

exec (@sql)

go

EXEC dbo.Sun5_OFAC_Export 'BUS'

If this works for you, then it's time to follow up some suggestions, in particular rbarryyoung's.

^{“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 6

That works - thanks Chris......so it was just doubling up the quotes basically?

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

richard (12/4/2008)
That works - thanks Chris......so it was just doubling up the quotes basically?

Cool, well done.

There were a couple of changes to make between the original and the final, but what you will find in practice is this:

It's a whole lot quicker and easier testing a chunk of code in QA/SMS than plonked into a sproc.

Always print the dynamic statement before attempting to execute it, and try executing the output of the print statement.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Chris Morris (12/4/2008)
It's a whole lot quicker and easier testing a chunk of code in QA/SMS than plonked into a sproc.
Always print the dynamic statement before attempting to execute it, and try executing the output of the print statement.

Just a "Meta"-tip to this tip: When you do this, set your output format to text in SSMS and under "Query Options..Results..Text" set the text output width to 8000 (that's the max).

[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]