Variable Table Names in SP ?

Question

Variable Table Names in SP ?

homebrew01

SSC Guru

Points: 55548
More actions
June 27, 2010 at 6:28 pm

#387225

I inherited a manual procedure from someone who left the company. Twice a month he runs all these manual steps that takes about 5 hours total. One of the steps involves using "copy-paste" to replace temporary table names in a stored procedure, run the sp, then copy-paste the next table name, run the sp ...... about 40 times ..... jeesh
tiny snippet of the 236 instances the table name (leads_382JCTFK ) is used, :
SELECT @Statecnt = count(*) from leads_382JCTFK where state = @state
------------6 segments
IF @Segment = 6
IF @walker = 0
BEGIN
UPDATE leads_382JCTFK SET rwa_segment = 1 WHERE rwa_id = @RWA_ID
SET @walker = 1
END
ELSE IF @walker = 1
BEGIN
UPDATE leads_382JCTFK SET rwa_segment = 2 WHERE rwa_id = @RWA_ID
SET @walker = 2
END
ELSE IF @walker = 2
BEGIN
UPDATE leads_382JCTFK SET rwa_segment = 3 WHERE rwa_id = @RWA_ID
SET @walker = 3
END
What would be a better way to do this ? The table "leads_382JCTFK" is an example, but there are 40+ similar names tables that have to be replaced then run in this SP.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Let's start this off with a little data analysis... Unless I'm totally off my rocker (heh... which is always a possibility :-D), all of the following code...

IF @Segment = 6

IF @walker = 0

BEGIN

UPDATE leads_382JCTFK SET rwa_segment = 1 WHERE rwa_id = @RWA_ID

SET @walker = 1

END

ELSE IF @walker = 1

BEGIN

UPDATE leads_382JCTFK SET rwa_segment = 2 WHERE rwa_id = @RWA_ID

SET @walker = 2

END

ELSE IF @walker = 2

BEGIN

UPDATE leads_382JCTFK SET rwa_segment = 3 WHERE rwa_id = @RWA_ID

SET @walker = 3

END

... can be replaced with the following greatly simplified code...

UPDATE dbo.leads_382JCTFK

SET rwa_segment = @walker + 1

WHERE Rwa_ID = @RWA_ID

AND @Segment = 6

SELECT @walker = @walker + 1

Now it's a simple matter of replacing table names and, if all the table names are, in fact, similar, then we should be able to do something like the following to find all the table names. Here's a little test done in TempDB so we don't blow anything up...

First, let's create 3 test tables just to make sure the code will work...

--===== Create some simple test tables in TempDB

USE TempDB

CREATE TABLE leads_382JCTFK

(RWA_Segment INT, RWA_ID INT, State CHAR(2))

CREATE TABLE leads_383JCTFK

(RWA_Segment INT, RWA_ID INT, State CHAR(2))

CREATE TABLE leads_384JCTFK

(RWA_Segment INT, RWA_ID INT, State CHAR(2))

Now, let's see if we can find all those tables...

SELECT Name AS TableName

FROM sys.Objects

WHERE Type = 'U'

AND Name LIKE 'Leads__%' ESCAPE '_'

So far, so good. Now, let's build some dynamic SQL that changes things like table names. I actually want each execution to recompile to prevent parameter sniffing and the like so I've intentionally NOT used sp_ExecuteSQL here. Also, I don't know what the rest of your code looks like so just take this as a starter. It may very well be that things like @walker and @Segment could be directly incorporated into the dynamic SQL depending on where in the code they're assigned. At any rate, the following code will give you the idea of what could be done to make your life a whole lot easier twice a month! 😛 As always, details are in the comments in the code. 😉

--===== Declare and Preset the parameters found in the original code

DECLARE @RWA_ID INT,

@Segment INT,

@walker INT

SELECT @RWA_ID = 3, --I just made this one up because nothing given in original code

@walker = 0, --or whatever,

@Segment = 6 --or whatever.

--===== Declare a dynamic SQL variable. This also helps avoid writing loops although

-- a loop wouldn't be a problem here. I just can't bring myself to writing one

-- if I can get away with it.

DECLARE @SQL VARCHAR(MAX)

--===== Build up the dynamic sql for each table using the same method to find the

-- table names that we did before. We also change "variable" data to "fixed"

-- data in the dynamic SQL. It makes the final code look a little odd but it

-- works great.

SELECT @SQL = (

SELECT CHAR(10)

+ 'UPDATE dbo.' + QUOTENAME(Name) + ' '

+ 'SET rwa_segment = ' + CAST(@Walker AS VARCHAR(10)) + ' + 1 '

+ 'WHERE Rwa_ID = ' + CAST(@RWA_ID AS VARCHAR(10)) + ' '

+ 'AND ' + CAST(@Segment AS VARCHAR(10)) + ' = 6'

FROM sys.Objects

WHERE Type = 'U'

AND Name LIKE 'Leads__%' ESCAPE '_'

FOR XML PATH('')

)

PRINT @SQL --Display the SQL that will be executed. You can comment this out in production... or not.

--EXEC (@SQL) --Commented out for safety here. When you feel OK about it, you can uncomment.

--===== Now, do like the original code did... Update @walker.

SELECT @walker = @walker + 1

--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)

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Wow ! What a great answer ! I'll give that a try.

Thanks

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Thanks. Let me know how it works out for you.

Also, make sure that if there is more "@Walker" code, that it continues to follow the same pattern of @walker + 1. If it doesn't, a small lookup table will easily fix the problem.

--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)

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

In the last code example, what does " SELECT CHAR(10)" and "FOR XML PATH('') " do in this case ?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

homebrew01 (6/28/2010)
In the last code example, what does " SELECT CHAR(10)" and "FOR XML PATH('') " do in this case ?

CHAR(10) inserts a line-break in the output result

FOR XML PATH('') concatenates all the statements into one, kinda concatenating all the row values of a column into one single row.. char and xml put together will produce UPDATE statement of each table prefixed with "LEADS" with a line break between each tables..

like :

update LEADS_blahblah

update LEADS_blahblah2

update LEADS_blahblah3

HTH 🙂

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

ColdCoffee (6/28/2010)
homebrew01 (6/28/2010)
In the last code example, what does " SELECT CHAR(10)" and "FOR XML PATH('') " do in this case ?
CHAR(10) inserts a line-break in the output result
FOR XML PATH('') concatenates all the statements into one, kinda concatenating all the row values of a column into one single row.. char and xml put together will produce UPDATE statement of each table prefixed with "LEADS" with a line break between each tables..
like :
update LEADS_blahblah
update LEADS_blahblah2
update LEADS_blahblah3
HTH 🙂

Spot on, CC. 😉 Couldn't have said it better myself.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 7

Jeff Moden (6/27/2010)
Now, let's see if we can find all those tables...
SELECT Name AS TableName
FROM sys.Objects
WHERE Type = 'U'
AND Name LIKE 'Leads__%' ESCAPE '_'

Since you're on 2005, this could be replaced with:

SELECT Name AS TableName

FROM sys.tables

WHERE Name LIKE 'Leads__%' ESCAPE '_'

Not saying that one is better than the other; this is just an alternative.

Edit: you might also be able to use a synonym to reference the necessary table, and then keep the update statements pointing to the synonym.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

All good ideas, Wayne. Thanks for the feedback.

--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)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Jeff Moden (6/29/2010)
ColdCoffee (6/28/2010)
homebrew01 (6/28/2010)
In the last code example, what does " SELECT CHAR(10)" and "FOR XML PATH('') " do in this case ?
CHAR(10) inserts a line-break in the output result
FOR XML PATH('') concatenates all the statements into one, kinda concatenating all the row values of a column into one single row.. char and xml put together will produce UPDATE statement of each table prefixed with "LEADS" with a line break between each tables..
like :
update LEADS_blahblah
update LEADS_blahblah2
update LEADS_blahblah3
HTH 🙂
Spot on, CC. 😉 Couldn't have said it better myself.

Thanks Jeff 🙂