Changing Column Names in a Store Prosedure

Question

Post reply

Changing Column Names in a Store Prosedure

Anton van Wyk

Grasshopper

Points: 17
More actions
April 8, 2004 at 7:34 am

#163328

I need help I want to change my Column name in my store pro dinamicly from where I am getting current date then moving back 6 mth's at a time wher the column name have to change how can I do this???? Any one.....................

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 1

Please first tell me. Are you currently using SQL Server Yukon?

Here is the answer to your question with assumption of SQL2K in use.

Create views with different date ranges. You should able to provide two different column names in the two different views. If you provide more details of what compell you to use different column names for two diffent date range; I might be able to suggest you couple of work arounds.

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

First in my proc each column in the table that I want to select I have named as Mx where x is either 1,2,3,4,5 - for example M6.

Then created this stored procedure to move backwards from the starting column of course a slight change can make it move forward as well

CREATE procedure myselect

@smonth int

as

declare @sql nvarchar(1000)

declare @M6 nvarchar(3)

declare @M5 nvarchar(3)

declare @M4 nvarchar(3)

declare @TM char(2)

If @smonth < 3 set @smonth = 3

if @smonth > 12 set @smonth = 12

set @TM = Cast(@smonth as char(2))

set @M6 = 'M' + @TM

set @smonth = @smonth - 1

set @TM = Cast(@smonth as char(2))

set @M5 = 'M' + @TM

set @smonth = @smonth - 1

set @TM = Cast(@smonth as char(2))

set @M4 = 'M' + @TM

set @sql = 'SELECT ' + @M6 +', ' +@M5 +', ' +@M4 +' From TestMonths'

exec sp_executesql @SQL

GO

Test in Query Analyzer as exec myselect 6

to test and get some new ideas

Now this needs some additional code to keep from creating a column name that does not exist in the table

Just hope this gives you an idea of what you might do

Good Luck

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]

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

If this is a one off....because you are querying a database that has 'been upgraded'....then copying/amending the SP is fine.

However......if this is a requirement because 'time has moved on and we need to query data in different (named)columns'...then you have poor data design...(it's not a good idea to name columns with reference to data values that may be contained therein...ie year2004data, year2003data, jandata, febdata, mardata, etc)....and would be best advised to upgrade your design.....to resolve this particular problem....and also others that are probably causing you either data bloat or code management problems....

At the moment you are seeing the thin end of the wedge of the problems stacked up against you.....watch out for the hammer that is hitting the fat-end!