Dynamic vs Static Stored Procedures

Dynamic vs Static Stored Procedures

Post by Fred Chatea » Thu, 15 Mar 2001 02:56:58



I would appreciate it if someone would explain what exactly is the
difference between a dynamic and a static stored procedure.

Specifically, if I am using a stored procedure which is basically nothing
more than a select statement in which I submit values for the where clause,
is this a dynamic procedure? If so, is there any advantage to it over
submitting the select statement directly in an ADO conn.execute directive?

--
Regards,

Fred Chateau
http://members.home.net/fchateau/

 
 
 

Dynamic vs Static Stored Procedures

Post by DaveSat » Thu, 15 Mar 2001 03:19:23


from a TSQL standpoint, if you use EXEC() or sp_executesql, then it is
considered dynamic SQL, i.e.
http://www.mssqlserver.com/faq/development-dynamicsql.asp.

As has been stated on this newsgroup seemedly hundreds of time, there are
security and performace issues with using dynamic SQL, as described above.

Your last questions seemed to asked whether you should use Stored Procs vs.
SELECT.  A stored procedure is just a predefined set of pre-compiled, T-SQL
code that can be executed by a variety of methods on demand.  You can
encapsulate security using SP's so that users can have rights to the SP, but
not to the underlying tables, unless you use dynamic SQL.

--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------


Quote:> I would appreciate it if someone would explain what exactly is the
> difference between a dynamic and a static stored procedure.

> Specifically, if I am using a stored procedure which is basically nothing
> more than a select statement in which I submit values for the where
clause,
> is this a dynamic procedure? If so, is there any advantage to it over
> submitting the select statement directly in an ADO conn.execute directive?

> --
> Regards,

> Fred Chateau
> http://members.home.net/fchateau/


 
 
 

Dynamic vs Static Stored Procedures

Post by Fred Chatea » Thu, 15 Mar 2001 03:44:52


"DaveSatz" wrote ...

Quote:> Your last questions seemed to asked whether you should use Stored Procs
vs.
> SELECT.  A stored procedure is just a predefined set of pre-compiled,
T-SQL
> code that can be executed by a variety of methods on demand.  You can
> encapsulate security using SP's so that users can have rights to the SP,
but
> not to the underlying tables, unless you use dynamic SQL.

I guess what I was really asking was whether dynamic referred to variables
in certain positions but not in others. As an example, maybe it would make a
difference in compilation if you used a variable for a table name, but might
not make a difference if the variable represented a column name or row ID,
or if the variable appeared in a different position such as the where
clause.

If any variable in any position a stored procedure causes a situation where
compilation does not occur in advance (what I imagine everyone is referring
to as dynamic SQL), then I assume that if security is not an issue, a stored
procedure would not be necessary for a select statement.

I would appreciate if you would confirm this, or correct any false
assumptions here.

--
Regards,

Fred Chateau
http://members.home.net/fchateau/

 
 
 

Dynamic vs Static Stored Procedures

Post by DaveSat » Thu, 15 Mar 2001 04:59:15


SQL Server will be able to determine a query plan for the query if you not
use EXEC() or sp_executesql like you would have to - to have a variable
table and/or column name.  If you use a variable in the where clause, i.e.


select *
from Products

SQL Server will be able to optimize if there are appropriate indices.

HTH


Quote:> "DaveSatz" wrote ...

> > Your last questions seemed to asked whether you should use Stored Procs
> vs.
> > SELECT.  A stored procedure is just a predefined set of pre-compiled,
> T-SQL
> > code that can be executed by a variety of methods on demand.  You can
> > encapsulate security using SP's so that users can have rights to the SP,
> but
> > not to the underlying tables, unless you use dynamic SQL.

> I guess what I was really asking was whether dynamic referred to variables
> in certain positions but not in others. As an example, maybe it would make
a
> difference in compilation if you used a variable for a table name, but
might
> not make a difference if the variable represented a column name or row ID,
> or if the variable appeared in a different position such as the where
> clause.

> If any variable in any position a stored procedure causes a situation
where
> compilation does not occur in advance (what I imagine everyone is
referring
> to as dynamic SQL), then I assume that if security is not an issue, a
stored
> procedure would not be necessary for a select statement.

> I would appreciate if you would confirm this, or correct any false
> assumptions here.

> --
> Regards,

> Fred Chateau
> http://members.home.net/fchateau/

 
 
 

1. Dynamic filter vs static filter...

I have got 25 subscribers. how should I create my publishers.

1 Publisher --> 25 subscriber
5 publisher where 1 Publisher -->  5 subscriber .

I have horizontal filters also.
which is good ? 1 publisher 1 static filter or 1 Publisher serving all
subscriber with dynamic filters.

Thanx in advance...
Shailesh

2. Old Accucobol 3.0 Database HELP!!!!!!!!

3. STATIC vs. DYNAMIC SQL and ODBC

4. Stranger things have happened.

5. Static vs Dynamic Recordsets

6. VB vs VB.NET vs VBA

7. performance of dynamic vs. static SQL

8. Help! 'Run time error '424' Object Required'

9. Dynamic SQL Vs Static SQL using Pro*C..

10. Dynamic vs. Static

11. Dynamic vs. Static Hashed Files

12. Dynamic Files vs Static Hashed - Performance Issues

13. Static vs. Dynamic ESQL