System Stored Procedures Method stored processes are packaged with SQL Server. Lots of strategies are accustomed to administer SQL Server, but some are utilities that could be profitablly employed by builders. They're world-wide, and will be termed from any database software with no their thoroughly skilled name. (They are all owned by dbo.) . They are all stored in the Grasp databases, and have the prefix sp_. This can be a purpose why it is considered unwise to call regional stored strategies with the sp_ prefix. They are often go through by viewing their Qualities inside the Question Analyzer.
The program-stored methods are grouped into these categories.
Class
Description
Lively Listing Procedures
Used to sign-up circumstances of SQL Server and SQL Server databases in Microsoft Home windows 2000 Active Listing.
Catalog Methods
Implements ODBC details dictionary functions and isolates ODBC applications from variations to underlying process tables.
Cursor Procedures
Implements cursor variable performance.
Databases Routine maintenance Prepare Techniques
Used to arrange Main upkeep tasks essential to ensure databases functionality.
Dispersed Queries Methods
Utilized to put into practice and regulate Distributed Queries.
Complete-Textual content Research Methods
Accustomed to put into action and question full-textual content indexes.
Log Transport Techniques
Used to configure and regulate log transport.
OLE Automation Procedures
Permits standard OLE automation objects for use inside of a normal Transact-SQL batch.
Replication Methods
Utilized to manage replication.
Safety Procedures
Used to deal with protection.
SQL Mail Procedures
Utilized to execute e-mail operations from inside of SQL Server.
SQL Profiler Techniques
Employed by SQL Profiler to watch performance and action.
SQL Server Agent Methods
Used by SQL Server Agent to handle scheduled and celebration-driven activities.
Technique Processes
Used for standard upkeep of SQL Server.
Net Assistant Methods
Employed by the net Assistant.
XML Methods
Useful dépanneur ordinateur portable domicile particuliers Paris for Extensible Markup Language (XML) textual content administration.
Basic Extended Methods
Gives an interface from SQL Server to exterior programs for several routine maintenance actions.
You may develop your very own technique-stored processes by adhering to the recommendations outlined over. But, Though it can be done to modify an current process sproc, it is better to name it with An additional title than to wipe out the program Variation.
Dynamic Queries are created and executed in just a saved course of action, and so are created typically by passing in textual content parameters and making a textual content SQL string. Then the string is executed utilizing the form EXEC (@createdstring). Dynamic query strategies are certainly not stored in cache, and native variables created from the dynamic query are usually not out there following the query is executed. To avoid wasting the question strategy, instead of the keyword EXEC, use sp_executesql. Also, with sp_executesql regional variables can be utilized each as INPUT and as OUTPUT parameters.
Autoexec processes, named autostart treatments is often produced by invoking the process saved method
sp_procoption procedurename, startup, real
Prolonged Retail store Methods
Prolonged stored procedures are auxiliary stored methods developed in the DLL working with C . They are really prefixed with xp_, and saved in the Grasp databases. When known as from an software database, they bust be absolutely referenced, using the Learn..xp_procname form. Before you decide to can invoke it, you will need to sign up the DLL with SQL Server using the syntax:
sp_addextendedproc [@functname=] course of action, [@dllname=] dll
Sources:
Information Getting Started with Sybase
This useful resource discusses Sybase databases management system.
Details: Accessing Saved Treatments
This source gives insightful info on accessing saved procedures.
Metro NY / NJ SQL Server Consultants
We focus is custom made databases computer software.