exec function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

exec function

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-01 : 10:28:25

In table

tbl_query
(id, sql)

field 'sql'
i have stored sql sentence (select * from ....)

and how can i run this sql sentence?

Exec (select sql from tbl_query where id = 1).

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 11:08:12

DECLARE @Var varchar(8000)

SELECT @Var = MIN(sql)
FROM tbl_query

WHILE @Var IS NOT NULL

EXEC @Var
SELECT @Var = MIN(sql)
FROM tbl_query
WHERE sql > @Var
END
...

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-02 : 10:34:39

visakh16, thank you for your answer, but unfortunatelly this doesn't do anything that would read sql sentence from tbl_query.

got anyother idea?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-02 : 11:05:22

declare @sql varchar(50)
select @sql = sql from tbl_query where id = 1
Exec (@sql)

When I see people storing executable sql, red flags are raised for me that the design may be...not optimal. If you want to expain the big picture you may get some alternative ideas.

EDIT:
Are you mike0913? Still working on the Mail Merge thing?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96242

Be One with the Optimizer
TG

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-02 : 11:12:42

:) thank you. why the red flag? :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-02 : 11:33:03

First I should say that I fall squarely in the camp of "all application database calls should be via parameterized Stored Procedures." So if your application design uses "in-line" straight-up sql statements, then I suppose storing them in the database makes it easier to maintain the code. You don't need to recompile the app to tweek the code.

But since your question is about executing code from within sql server, code that is stored in the database then:

Because most database systems have already desinged methods of storing code for execution. Sql Server calls them Stored Procedures. There are a lot of advantages to using SPs including plan caching, all executing code falls under the security of the SP, parameterized SPs significantly reduces security breaches.

Maintaining a seperate sql statement "customized" for that particular row of data is just not efficient. It also requires that whoever the users are that are entering the data also need to know sql. Not just how to construct a statement but also optimize it. Highly unlikely that you will have a table full of perfectly running code.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources