CODEWITHSUNDEEP
sql-serverstored-proceduresdynamic-columns
bbcompent1
bbcompent1

Reputation: 494

Dynamically add column with today's date

I am trying to create a stored procedure that will dynamically add a column with today's date every day. The reason for this is because each day I get a new file with a new count for each item. They want to keep a running historical total by day. This is the only way I can think I can do this, unless one of you has a suggestion for this schema design. Thank you.

Upvotes: 0

Views: 1896

Answers (1)

RU Ahmed
RU Ahmed

Reputation: 558

Here is Your Query:

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[usp_AddCollumByDate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE usp_AddCollumByDate
GO

create procedure usp_AddCollumByDate
as
begin
    declare @sql varchar(max);
    declare @toDay varchar(20)=CONVERT(varchar(10),GETDATE(),112);
    set @sql = 'ALTER TABLE MyTable ADD  D' +  @toDay + ' varchar(max); '
    print @sql
    EXECUTE(@sql);
end

exec usp_AddCollumByDate

It will add column every day (for today's example its add):

ALTER TABLE MyTable ADD  D20160920 varchar(max);

Now Listen Carefully:

  1. I have add a Letter D in front of today's date. It is because 20160920 is a INVALID column name. You have to start a column with a letter(for example An3, a33...)
  2. I have used the style 112 which give the date as yyyymmdd, You can not use 11,111,10 and some other style because its give the date with - like mm-dd-yyyy and it is an INVALID column name. You Can not use - in a column name.Here is more style , Try to use yyyymmdd,yymmdd style

Upvotes: 1

Related Questions