Dela via

  • Artikel

Extended Stored Procedures

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.

Extended stored procedures let you create your own external routines in a programming language such as C. The extended stored procedures appear to users as regular stored procedures and are executed in the same way. Parameters can be passed to extended stored procedures, and extended stored procedures can return results and return status.

Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

Note

Extended stored procedures should not be used to instantiate the Microsoft .NET Framework common language runtime and execute managed code. This scenario will not be supported in future versions of SQL Server. CLR Integration provides a more robust and secure alternative to writing extended stored procedures.

After an extended stored procedure has been written, members of the sysadmin fixed server role can register the extended stored procedure with the instance of SQL Server, and then grant permission to other users to execute the procedure. Extended stored procedures can be added only to the master database.

Note

Extended stored procedures may produce memory leaks or other problems that reduce the performance and reliability of the server. You should consider storing extended stored procedures in an instance of SQL Server that is separate from the instance that contains the referenced data. You should also consider using distributed queries to access the database. For more information, see Distributed Queries.

Security

Before adding extended stored procedures to the server and granting execute permissions to other users, the system administrator should thoroughly review each extended stored procedure to make sure that it does not contain harmful or malicious code.

Additional security guidelines include the following:

  • Validate all user input.

  • Do not concatenate user input before validating it.

  • Never execute a command constructed from unvalidated user input. For more information, see SQL Injection.