SQL Server SP Parameter Default Value

SQL Server does not allow creating a stored procedure with parameter default values that are not constant. This can be a problem if you want to give a parameter a default value that is predictable but not constant such as giving a datetime parameter a default value of the current time.

There are two ways to resolve this. One is solution but it's not very flexible. The other is a workaround but can be used in more situations. First the solution. If the parameter is used as a minimum or maximum value check, you can totally ignore the value if it's null.

Here is an example of the first solution. Let's say we want to write a stored procedure to get all records from an Activity table that occured from one point in time to another. In this case we want to include all records if the boundry variable is not specified. In the example below we simply do this by checking for NULL and using the logical OR.

CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME

AS
 BEGIN
   SELECT * FROM Activity
   WHERE (@From is null or LogTime >= @From)
   and (@To is null or LogTime <= @To)
 END

Another way to do this that is more flexible is to check if the parameter is null and in that case set it equal to whatever value you wish.

For example, in the code below I check if the @To variable is null and if it is I set it equal to the current time.

CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME

AS
 BEGIN
   IF @To is null
   BEGIN
      SET @To = GetDate()
   END

   SELECT * FROM Activity
   WHERE (@From is null or LogTime >= @From)
   and LogTime <= @To
 END

ASP .NET Connection Strings in Web.config File

ASP .NET offers an elegant way to store data source connection creditentials. Connection strings should be stored in a .config file, commonly and by default the Web.config file.

Firstly, what are connection strings? Connection strings are strings of text that contain information about a data source used in the code for data access. Information contained in a connection string includes the data source name, data source address, security mode, login creditentials, and the data source type.

There are a couple reasons why the Web.config file is a good place to store connection. One, ASP .NET is automatically configured to never display a file with a .config extension. So you can assume a level of security when storing login information in the Web.config. In addition, ASP .NET can be configured to encrypt the Web.config file, which would add more security. Nonetheless, you can be assured that any web request to a .config file would result in an error. Secondly, using Web.config allows a central way to manage information without requiring digging into the code nor code modification.

Below are examples of how connection strings would be used:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
 <connectionStrings>
  <add name="connectionName" connectionString="Data Source=serverName;
  Initial Catalog=databaseName;User Id=username;Password=password;"
  providerName="System.Data.SqlClient"/>
</connectionStrings>
...
</configuration>

To retrieve the above connection string in C# code:

string connStr = ConfigurationManager.ConnectionStrings["connectionName"];

To use the connection string directly in an ASP .NET page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:connectionName %>"
  SelectCommand="SELECT * FROM [Employees]" />