Tally Table

Question

Post reply

Tally Table

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
January 6, 2010 at 3:29 pm

#208708

Hello Everyone
I need some help with a tally table.
I want to be able to query a list table, get the list of a few ID's and then fire off a stored procedure that accepts the IDs one at a time.
I want to replace this mess with code using a tally table. The values are hard coded into a single sproc. I want to replace these, so that when a new record is added to the list table, I will not have to modify this sproc to add one more to the list, which would be _8
EXEC <SprocName><parameter_1>
EXEC <SprocName><parameter_2>
EXEC <SprocName><parameter_3>
EXEC <SprocName><parameter_4>
EXEC <SprocName><parameter_5>
EXEC <SprocName><parameter_6>
EXEC <SprocName><parameter_7>
I would like to use a tally table to store the ID's from the list table, and then execute the stored procedure. unless someone has a better idea and way of performing this task.
Thanks in advance
Andrew SQLDBA
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I believe this article by Jeff Moden will assist you:

http://qa.sqlservercentral.com/articles/T-SQL/63003/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

The Tally table is not a panacea and isn't good for this. Dynamic SQL would be good for this. So would an (ugh!) cursor.

--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)
Intro to Tally Tables and Functions

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

Never, ever will I use a Cursor. But thanks for the info.

Andrew SQLDBA

Herman van Midden SSChasing Mays Points: 643 More actions · Answer 4

OK, So you don't want to use a cursor? Even better!

You don't need to use a tally table either. I came up with this solution. I created a List table to provide some values for the parameters.

Replace the Print with Execute(@SqlTmp) and <SprocName> with your procedure to get it to work

CREATE TABLE dbo.List(ID int)

GO

INSERT List Values (1);

INSERT List Values (2);

INSERT List Values (3);

DECLARE @Count INT

DECLARE @MaxRows INT

DECLARE @Sql varchar(max)

DECLARE @SqlTmp varchar(max)

SET @Sql='EXEC <SprocName> @Param=@ID'

SET @Count=1

SELECT @MaxRows=COUNT(*) FROM List

WHILE @Count <= @MAxRows

BEGIN

SELECT TOP 1 @SqlTmp= Replace(@Sql,'@ID',ID)

FROM(SELECT ROW_NUMBER() OVER (ORDER BY ID) as Cnt, ID

FROM List) AS A

WHERE Cnt = @Count

Print @SqlTmp

--Execute (@SqlTmp)

SET @Count = @Count + 1

END

Regards

Herman

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Herman

Thank you so very much for your time that you spent writing this.

I should have stated that I cannot use Dynamic SQL either. That is my fault totally.

No Loops, no Dynamic SQL, and absolutely no Cursing.....uhmmm I mean Cursors. 😀

My only other option is to use SSIS. Which in turn will use a loop, but on a little different way. There I can use a list table, and let it execute the sproc over and over. Giving me a "go" / "no go" at the end of each statement execution.

Thanks so much for your time and effort

Andrew SQLDBA

Al-279884 Old Hand Points: 383 More actions · Answer 6

With your restrictions the only thing you may be able to do is to modify the <SprocName> stored procedure to work with rowset operations (inner joins to your list table), rather than one single ID provided as a parameter. I would like to know what happens within that stored procedure for a better answer.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

Jeff Moden (1/6/2010)
The Tally table is not a panacea and isn't good for this. Dynamic SQL would be good for this. So would an (ugh!) cursor.

Are you feeling ok Jeff?:sick:

That is two admissions to a cursor being ok for the task at hand within the past few days. 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281324 More actions · Answer 8

AndrewSQLDBA (1/6/2010)
Hello Everyone
I need some help with a tally table.
I want to be able to query a list table, get the list of a few ID's and then fire off a stored procedure that accepts the IDs one at a time.
I want to replace this mess with code using a tally table. The values are hard coded into a single sproc. I want to replace these, so that when a new record is added to the list table, I will not have to modify this sproc to add one more to the list, which would be _8
EXEC <SprocName><parameter_1>
EXEC <SprocName><parameter_2>
EXEC <SprocName><parameter_3>
EXEC <SprocName><parameter_4>
EXEC <SprocName><parameter_5>
EXEC <SprocName><parameter_6>
EXEC <SprocName><parameter_7>
I would like to use a tally table to store the ID's from the list table, and then execute the stored procedure. unless someone has a better idea and way of performing this task.
Thanks in advance
Andrew SQLDBA

One at a time makes this very difficult since that defines RBAR.

Must the proc run each of the parameters sequentially?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 9

AndrewSQLDBA (1/7/2010)
Never, ever will I use a Cursor. But thanks for the info.
Andrew SQLDBA

Why not? There are cases (admittedly rare) where cursors are the *right* tool for the job. If that is the right tool - why make your job that much harder because you refuse to use it?

In this particular case - using a cursor or dynamic SQL are your best options. The next best option would be to rewrite the procedure to work on the set of values instead.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

mister.magoo SSC-Forever Points: 47068 More actions · Answer 10

--= set up some test data

declare @ids table(id int identity(1,1))

insert @ids default values

--= process the ids through a stored proc

DECLARE @idlist varchar(100),@id int

SELECT @idlist = list

from (select convert(varchar,id)+',' from @ids for xml path(''))a(list)

WHILE @idlist != ''

BEGIN

SELECT @id = CONVERT(int,LEFT(@idlist,CHARINDEX(',',@idlist,1)-1))

EXEC myproc @id=@id

SELECT @idlist = SUBSTRING(@idlist,CHARINDEX(',',@idlist,1)+1,LEN(@idlist))

END

I know you don't want loops, but the truth is this problem needs a loop as you can't EXEC from each row of a select....

Anyway, I would imagine that the cost of the stored proc will far outweigh the cost of looping ....

The IO on this query is : I don't think you can get lower than 1 read?

Table '#6B79F03D'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

CirquedeSQLeil (1/7/2010)
Jeff Moden (1/6/2010)
The Tally table is not a panacea and isn't good for this. Dynamic SQL would be good for this. So would an (ugh!) cursor.
Are you feeling ok Jeff?:sick:
That is two admissions to a cursor being ok for the task at hand within the past few days. 😉

Heh... yeah, I'm alright. I would prefer Dynamic SQL for this one (especially since it's 2k5 we're talking about) but I wouldn't pork chop a developer for using a nice forward only, read only, static cursor for this one.

Of course, Jeffrey Williams said the best solution... rewrite the bloody proc so you don't have to call it in a RBAR manner to begin with. 😀 Of course, we don't know what the sproc actually does and that's a possible fault on the part of Andrew or the people he works for.

@andrew... perhaps you should tell us what the proc does... there's likely a set based solution that will keep all forms of RBAR at bay here.

--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)
Intro to Tally Tables and Functions

SQLRNNR SSC Guru Points: 281324 More actions · Answer 12

Jeff Moden (1/7/2010)
CirquedeSQLeil (1/7/2010)
Jeff Moden (1/6/2010)
The Tally table is not a panacea and isn't good for this. Dynamic SQL would be good for this. So would an (ugh!) cursor.
Are you feeling ok Jeff?:sick:
That is two admissions to a cursor being ok for the task at hand within the past few days. 😉
Heh... yeah, I'm alright. I would prefer Dynamic SQL for this one (especially since it's 2k5 we're talking about) but I wouldn't pork chop a developer for using a nice forward only, read only, static cursor for this one.
Of course, Jeffrey Williams said the best solution... rewrite the bloody proc so you don't have to call it in a RBAR manner to begin with. 😀 Of course, we don't know what the sproc actually does and that's a possible fault on the part of Andrew or the people he works for.
@andrew... perhaps you should tell us what the proc does... there's likely a set based solution that will keep all forms of RBAR at bay here.

Agreed on the proc rewrite - hopefully Andrew will get us more information...

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

AndrewSQLDBA (1/7/2010)
...
I should have stated that I cannot use Dynamic SQL either. ...

Why not? It's perfectly safe as long as you do it correctly.

Here is how I would do it with Dynamic SQL, and no evil Cursors or While loops:

CREATE PROC ExecuteFromParamList

AS

/*

Execute a Sproc once for each parameter in your table

*/

--====== Declare and initialize the command string

DECLARE @sql as NVARCHAR(MAX)

SET @sql=''

--====== Build the command string from the parameter table

SELECT @sql = @sql + ' EXEC <SprocName> ' + Parameter + ';

'

FROM YourParameterTable

--====== First Print(for diagnostics) and then Execute the command string

PRINT @sql-- NOTE: only prints the first 4000 chars

EXEC dbo.sp_executesql @sql

You should note also, that if you do not want to execute this AS dynamic SQL, that you could use a slight modification of this routine as an Insert/Update/Delete trigger on your parameters table to just regenerate your current fixed-style stored procedure instead, just updated with the correct number and values of the parameters.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Jeff Moden (1/7/2010)
... there's likely a set based solution that will keep all forms of RBAR at bay here.

Heh. Well, all but one ... 😀

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