Purpose of creating SQL Server stored procedures at run time

15 pts.
Tags:
Microsoft SQL Server 2000
RunTime
SQL Server development
SQL Server stored procedures
Hi Experts, I am a programmer in SQL. Please explain me the purpose of creating stored procedures during run time and it's effects on performance and memory. Please provide me if there is any best alternative for this. Thanks in advance

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I'm afraid we cannot give any alternative without knowing what you want to accomplish (what is your purpose ?)
    69,510 pointsBadges:
    report
  • Skothamasu
    Hi, Thaks for your reply. I just wanted to have the basic knowledge of the purpose of dynamic stored procedures. That's it.
    15 pointsBadges:
    report
  • Kccrosser
    I think we are still confused by your question. Are you asking why/when would you dynamically create a stored procedure at run time, or why would you use dynamic SQL at run time? The two questions are quite different. Dynamically created stored procedures are most often used from client-side applications, where you might define a SQL query function (in VisualBasic, for example), which is then dynamically compiled at run time and exists only during the application session. These are often used by applications that are trying to remain "database neutral" - the code is dynamically built and compiled with the appropriate database engine syntax and other quirks at run time based on the database connection. Usually the code itself isn't "dynamic" - it is created as static code and is only dynamic because it is compiled at run time. I am not sure I see many uses for generating and compiling "dynamic procedures" in native Transact-SQL or native PL/SQL (for Oracle). Building and executing dynamic SQL is vastly different and heavily used. Generally, we use dynamic SQL when the native SQL environment doesn't easily allow a static SQL expression to be used. For example, you cannot use static SQL expressions to select the table to use for a query at run time. If I need to run a query against one of several different tables, using a table name as a parameter at run time, the only effective way to do it is to dynamically create and execute the SQL statement. Tools for migrating, merging, maintenance, etc. tables generally have to use dynamic SQL, as otherwise each reference to a specific table would have to be hard-coded and compiled individually. (Ok - technically, there are ways to do this without dynamic SQL, but they are much harder and more complex.) The performance issues with any form of dynamic sql has to do both with the cost of doing the dynamic compilation and with the optimization of the underlying queries. If you are dynamically compiling a procedure to use once, that is hugely inefficient. If you are compiling a procedure dynamically, but will then call that procedure 1000 times, the initial compilation overhead may be insignificant. I may be wrong, but I believe the actual optimization of a dynamically compiled procedure and a dynamic SQL statement are roughly equivalent. If I was seriously worried about performance, I would be more likely to try to figure out how to minimize any use of dynamic code than to worry about dynamic SQL vs dynamic procedures.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following