SQL Server : Store Procedure

Normal stored procedures

We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.

A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead. The following example demonstrates this behavior.

USE AdventureWorks2008R2; GO CREATE PROCEDURE dbo.sp_who AS     SELECT FirstName, LastName FROM Person.Person; GO EXEC sp_who; EXEC dbo.sp_who; GO DROP PROCEDURE dbo.sp_who; GO 

Using an explicit schema qualifier also provides a slight performance advantage. Name resolution is slightly faster if the Database Engine does not have to search multiple schemas to find the procedure.

Temporary stored procedures

I'm using a technique similar to what I used for PostgreSQL for SQL server. I'm just creating a temporary stored procedure, and then calling it lots of times. I know you could also create a stored procedure on the server to do this, but here's how you might to it with a temporary one:

SET NOCOUNT ON GO CREATE PROC #tempInsertProc   @id integer AS   INSERT INTO foo (bar) VALUES (@id) GO EXEC #tempInsertProc 10 GO EXEC #tempInsertProc 11 GO EXEC #tempInsertProc 12 GO DROP PROC #tempInsertProc GO SET NOCOUNT OFF GO 

Temporary stored procedures on Microsoft SQL Server are prefixed with a pound sign #. One pound sign means that its temporary within the session, two pound signs ## means its a global temporary procedure, which can be called by any connection to the SQL server during its lifetime.

Why temporary procedures?

Your probably wondering why create temporary procedures, when you can just create a permanent stored procedure? In most cases its probably better to use a permanent SP, but if your like me, and don't like putting too much logic in the DB, but need to use a stored procedure, then these are one way to go.

Extended stored procedures

Important

(This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead.)

An extended stored procedure is a C or C++ DLL that can be called from Transact-SQL using the same syntax as calling a Transact-SQL stored procedure. Extended stored procedures are a way to extend the capabilities of Transact-SQL to include any resources or services available to Microsoft Win32 applications.

In other words you can create a Extended Stored Procedure in your custom language (VC++, VB, Delphi). These dll's can use API's provided by Open Data Services to interact, control and enhance the functionality of SQL server to provide the functionality you might require.

Packaged as dynamic-link libraries (DLLs), extended stored procedures provide a way to extend SQL Server functionality through functions developed by using C/C++, the Open Data Services API, and the Microsoft Win32® API. These functions can send result sets and output parameters back to the client from a variety of external data sources.