Using Optional Predicates in a Stored Procedure

The following Stored Procedure example demonstrates how optional predicates can be used in one statement based upon the parameters that are passed to the Stored Procedure.  The only caveat is that the columns cannot be NULLABLE as the predicate will not be evaluated correctly when a value is NULL, as NULL is not equivalent to NULL.  For more details regarding the joys of NULL’s refer to http://www.sqlservercentral.com/columnists/pward/malefemaleandtheotheronenull.asp.

CREATE PROC dbo.usp_Orders
@CustomerID NCHAR(10) = NULL,
@EmployeeID INT = NULL
AS

SET NOCOUNT ON

SELECT  OrderID
FROM  Northwind.dbo.Orders
WHERE  CustomerID = COALESCE(@CustomerID, CustomerID)
AND  EmployeeID = COALESCE(@EmployeeID, EmployeeID)

GO

In this example the Stored Procedure can be called in one of three ways

ie.

EXEC dbo.usp_Orders @EmployeeID = 1
EXEC dbo.usp_Orders @CustomerID = ‘VINET’
EXEC dbo.usp_Orders @CustomerID = ‘VINET’, @EmployeeID = 5

Movie of How to Use the SSIS For Each Container

I have recently been demonstrating SQL Sever Integration Services (SSIS) to a number of people and there seems to be some confusion on how the (SSIS) For Each Container can be used to to perform an operation on a collection. Rather than trying to document how to use the SSIS For Each Container I have used Camtasia Studio (http://www.techsmith.com/camtasia.asp) to record the use of the For Each Container to load all files in a specified directory. The movie file illustrating the SSIS For Each Container can be downloaded from http://www.wardyit.com/video/SSISForEachContainer.wmv.

Register Server in Enterprise Manager Using SQLDMO

A question I was recently asked is how the SQLDMO object can be used to register Servers and Server Groups in SQL Server Enterprise Manager.  The following VBScript snippet demonstrates adding a new Server Group and adding a Server to the newly created Server Group:

~~~

Dim oApplication
Dim oServerGroup

Set oApplication = CreateObject(“SQLDMO.Application”)
Set oServerGroup = CreateObject(“SQLDMO.ServerGroup”)
Set oRegisteredServer = CreateObject(“SQLDMO.RegisteredServer”)

‘ Add a new Server Group in Enterprise Manager called
‘ NewGroup
oServerGroup.Name = “NewGroup”
oApplication.ServerGroups.Add oServerGroup

‘ Register the SQL Server instance so that it uses
‘ Windows Authentication
oRegisteredServer.Name = “WARDYIT01”
oRegisteredServer.UseTrustedConnection = 1 ‘ 1 = True

‘ Add the registered server to the Serve Group NewGroup
Set DefaultGroup = oApplication.ServerGroups(“NewGroup”)
DefaultGroup.RegisteredServers.Add oRegisteredServer

Set oRegisteredServer = Nothing
Set oServerGroup = Nothing
Set oApplication = Nothing

~~~

GETDATE() in a UDF

In SQL Server 2000 Built-in nondeterministic functions were not allowed in the body of a user-defined functions (UDF).  If you attempted to create a UDF that used GETDATE() you received an error similar to:

Server: Msg 443, Level 16, State 1, Procedure ufn_Getdate, Line 5
Invalid use of ‘getdate’ within a function.

In SQL Server 2005 nondeterministic Built-in functions can be used in the body of a UDF with the exception of the following Built-in functions NEWID, RAND, NEWSEQUENTIALID, and TEXTPTR.

This means that you can now use GETDATE() inside a UDF.  For example:

CREATE FUNCTION dbo.ufn_Getdate()
RETURNS DATETIME
AS

BEGIN
RETURN GETDATE()
END

GO

SELECT dbo.ufn_Getdate() AS TheDate

Returns:

TheDate
———————–
2006-02-15 14:44:57.800

(1 row(s) affected)

 

Replication and QUOTED_IDENTIFIER

Recently I was contacted by a client who migrated a new Stored Procedure to production and wondered why replication all of a sudden ‘broke’ when the new Stored Procedure was added to the publication.  The reason is that the Stored Procedure was using Quoted Identifiers ie. double quotation marks (“a”).

This problem occurred because the Replication Snapshot Agent sets the QUOTED_IDENTIFIER option to ON, regardless of the actual setting.  Hence as the Stored Procedures was using double quotation marks, the default behaviour of the Distribution Agent was to use double quotation marks for identifiers only.

The following TSQL script illustrates this issue:

— Will Fail
SET QUOTED_IDENTIFIER ON
GO

IF “a” = “a” PRINT ‘does not work’
GO

— Will Succeed
SET QUOTED_IDENTIFIER OFF
GO

IF “a” = “a” PRINT ‘works’
GO

Moving Objects to Another Schema

In SQL Server 2005 all objects in a database are now located in schemas and are not owned by individual users. In order to move an object from one schema to another in the same database the ALTER SCHEMA command can be used.

For example to move the HumanResources.Department table to the Sales schema you can use the following command:

USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department