CODEWITHSUNDEEP
sql-serverdynamic-sql
user2206750
user2206750

Reputation: 1

how to create dynamic columns with date format

I need to create a table dynamically on daily basis, the columns are as follows.

COLUMN1, COLUMN2, (SYSDATE AS) COLUMN3, (SYSDATE-1) COLUMN4, (SYSDATE-2) COLUMN5,..

Kindly suggest the possible ways.

Upvotes: 0

Views: 79

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176234

Yes it is achievable, but Curse and Blessing Dynamic SQL by Erland Sommarskog

CREATE TABLE @tbl

The desire here is to create a table of which the name is determined at run-time.

If we just look at the arguments against using dynamic SQL in stored procedures, few of them are really applicable here. If a stored procedure has a static CREATE TABLE in it, the user who runs the procedure must have permissions to create tables, so dynamic SQL will not change anything. Plan caching obviously has nothing to do with it. Etc.

Nevertheless: Why? Why would you want to do this? If you are creating tables on the fly in your application, you have missed some fundamentals about database design. In a relational database, the set of tables and columns are supposed to be constant. They may change with the installation of new versions, but not during run-time.

Sometimes when people are doing this, it appears that they want to construct unique names for temporary tables. This is completely unnecessary, as this is a built-in feature in SQL Server. If you say:

CREATE TABLE #nisse (a int NOT NULL)

then the actual name behind the scenes will be something much longer, and no other connections will be able to see this instance of #nisse.

If you want to create a permanent table which is unique to a user, but you don't want to stay connected and therefore cannot use temp tables, it may be better to create one table that all clients can share, but where the first column is a key which is private to the client. I discuss this method a little more closely in my article How to Share Data between Stored Procedures.

Upvotes: 1

Related Questions