Template for SQL Server Stored Procedures

Talking with our in house DBA, it was mentioned that DROP/CREATE is a bad model to follow when updating stored procedures. However, this is the model used in the stored procedure template provided by Visual Studio 2005/2010. Here is a modified template that creates an empty stored procedure if it doesn’t exist and performs an ALTER on all subsequent calls.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name')
BEGIN
exec sp_executesql N'CREATE PROCEDURE [dbo].[Stored_Procedure_Name] AS select 1'
END
GO
ALTER Procedure Stored_Procedure_Name
(
  @parameter1 int = 5,
  @parameter2 datatype OUTPUT
)
AS
GO

GRANT EXEC ON Stored_Procedure_Name TO PUBLIC

GO

Enterprise Library Data Access Application Block + SQL Optional Parameters

The Enterprise Library Data Access Block provides a number of ways of calling a stored procedure.

Database _db = DatabaseFactory.CreateDatabase(<Connection String Name>);

Let’s now assume that we have a stored procedure usp_MyStoredProc that takes a single parameter @param1. If this is a require parameter, both of the following statement blocks will result in the same outcome:

public DataSet CallMyStoredProc( int value ){
return _db.ExecuteDataSet( “usp_MyStoredProc”, value )
}

public DataSet CallMyStoredProc( int value ){
DbCommand command = _db.GetStoredProcCommand(“usp_MyStoredProc” );
_db.AddInParameter( command, “param1”, value );
return _db.ExecuteDataSet( command );
}

However, now let’s assume that @param1 is an optional parameter that we don’t want to pass in from our data access method, we are limited in our options.

public DataSet CallMyStoredProc(){
return _db.ExecuteDataSet( “usp_MyStoredProc”)
}

This method will fail with an exception because the parameters provided to the stored procedure (none) don’t match the parameter count for the stored procedure (1).

Creating an actual DbCommand and executing it, however, will not cause an exception. In this scenario, the following code block will work without causing an exception:

public DataSet CallMyStoredProc( int value ){
DbCommand command = _db.GetStoredProcCommand(“usp_MyStoredProc” );
return _db.ExecuteDataSet( command );
}