executinginstructions in the select statement

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)

executinginstructions in the select statement

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2007-06-06 : 18:31:41

hi I have a table myTable with the following characteristics:
idCol textCol
0 create view1...
1 create view2...
...
...

If I wanted to execute each of the statements, I wonder if there is a way to execute "on the fly"
something like:
select exec(idCol) from myTable

thank you

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-06 : 19:38:32

The only way I know how to accomplish this is by using a cursor.


DECLARE MY_CURSOR Cursor
FOR 
Select IDcol,TextCol
from Mytable

Open My_Cursor --- (remember to CLOSE IT LATER)
--- We need to make containers for the Cursor Info
DECLARE @ID Int, @Text text

Fetch NEXT FROM MY_Cursor INTO @ID,@Text
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
exec(@Text)

Fetch NEXT FROM MY_Cursor INTO @ID,@Text
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Now I must ask, why would you need this?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-06 : 19:58:32

indeed. creating objects on the fly leads to a shaky foundation to develop on.

also, anyone with rights to that table could inflict pretty heavy damage on your system. what if someone inserts a row with the text "drop database Accounts" ?

elsasoft.org

heze
Posting Yak Master

192 Posts

Posted - 2007-06-06 : 20:21:51

I have a table which is the union of 14 entities, ie I have 14 tables in one, therefore, in order to make the queries simpler when dealing with this table I am partitioning the big table into 14 smaller "tables" or views,

j,v,do you have any comment on an alternative way to accomplesh this?

second, instead of copynig and pasting 14 times and changing the names of the views I just put everything into dynamic sql, third, whenever I want to maintian them or clean the database I can allways open one single file and execute everything with one button so this gives me an easy way to "source safe" all my code instead something ever happens to the computer, it is all warehousing and it is in a different server as the transactional databases so I dont need to worry for other than the actual schema which I have in scripts

thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 10:36:39

Why do you need such a design where queries are stored in a column?
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 12:38:20

Can you try table partitioning instead??

[url]http://msdn2.microsoft.com/en-us/library/ms345146(SQL.90).aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources