Stored Proc Iterating with Cursor and While? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc Iterating with Cursor and While?

Author

Topic

the_zaddik
Starting Member

3 Posts

Posted - 2007-06-12 : 11:22:39

Hello, I have a stored procedure that creates a temp table with a year of values, then goes through month by month calculating totals and percents. I want to make part of it a separate procedure, and just call it passing column names. The problem is that there are 9 columns to calculate for each month. What is the best way to iterate through the columns? (I dont want to write out the same query tons of times in one procedure)

Calling Procedure like this:
WHILE ( something )
BEGIN
SP_FOO(SomeColumName,SomeOtherColumn,month,year)
END

This is the text of the SP to calculate
UPDATE ##TEMPTABLE
SET @SomeColumName= COUNTS.SURVEYSSENT, @SomeOtherColumn = COUNTS.SURVEYSSENTSCORE
FROM ##TEMPTABLE SUMM,
(
SELECT
FROM
(
SELECT
FROM
WHERE
MONTH(DATE_SENT) = @month
AND YEAR(DATE_SENT) = @year
) SENT,
(
SELECT
FROM
WHERE
REPORTMONTH = @month
AND REPORTYEAR = @year
) RET
WHERE
) COUNTS
WHERE

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-12 : 15:15:36

Without drinking some alcohol to decipher what you want, might this be accomplished by creating a function that returns a table?

the_zaddik
Starting Member

3 Posts

Posted - 2007-06-12 : 16:13:00

quote:
Originally posted by rudesyle

Without drinking some alcohol to decipher what you want, might this be accomplished by creating a function that returns a table?

Rudestyle, indeed!

TABLE survey_yearly_metric
datapoint1month1 int,
datapoint2month1 int,
datapoint3month1 int,
datapoint4month1 int,
datapoint5month1 int,
datapoint6month1 int,
and so on till month12

now i have a stored proc that loads in some data to a temp table and begins calculating the values. This procedure calls another one to do some update.

I want to have a way to procedurally pass datapoint1month1 column name to the nested stored procedure.

Is there a way to do this?

the_zaddik
Starting Member

3 Posts

Posted - 2007-06-13 : 10:40:56

I got the answer from another post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79529&SearchTerms=select,column,name

SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources