You are hereMS SQL Server: Functions, Stored Procedures, Dates, etc.

MS SQL Server: Functions, Stored Procedures, Dates, etc.

By edwin - Posted on 19 June 2010

I'm happy to have learned how to use stored procedures(SP) and user-defined functions(UDF) recently. For a while now I have been using SELECT statements, but when I was asked to give a programmer the ability to call a query, I knew that a stored procedure would work better. A stored procedure lives on the SQL Server and can be called up over and over. Also, it allows different parameters to be used every time the SP is called.

EXEC sp_givemedata(param1,param2,param3)

In my case, I had a simple request to return the list of UniqueIDs from a table called Documents.

SELECT UniqueID
  FROM Documents
WHERE FaxDIDNum = '5550001'

I converted this into a stored procedure by simply wrapping the SELECT statement into a CREATE PROCEDURE command.

CREATE PROCEDURE dbo.sp_GetListOfUniqueIDs 
   (
     @FaxDIDNum VARCHAR(11)
   )
AS
SELECT UniqueID
  FROM Documents
WHERE FaxDIDNum = @FaxDIDNum 
GO
EXEC sp_GetListOfUniqueIDs '5550001'
GO
DROP PROCEDURE dbo.sp_GetListOfUniqueIDs;
GO

The last EXEC and DROP statements helped me re-work and test the query in SQL Server Management Studio.

Things got more complicated when the customer asked me to allow the paramater the ability to use more than one value.

EXEC sp_GetListOfUniqueIDs '5550001,5550002,5550003'

Google found more than one way to skin this cat. I opted for a rather old solution; create a split() function that turns a comma-delimited string into a table. I found the following example that worked nicely.

IF OBJECT_ID (N'dbo.Split', N'FN') IS NOT NULL
    DROP FUNCTION dbo.Split;
GO
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
AS       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
 
    SELECT @idx = 1       
        IF len(@String)<1 OR @String IS NULL  RETURN       
 
    while @idx!= 0       
    begin       
        SET @idx = charindex(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
        else       
            SET @slice = @String       
 
        IF(len(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       
 
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       
    end   
RETURN
end

So, how do I use this new function. I found that a SELECT statement could be used to return the table.

SELECT * FROM dbo.Split(@FaxDIDNum,',')

WHERE in the original SELECT statement becomes WHERE IN to handle multiple variables.

SELECT UniqueID
  FROM Documents
WHERE FaxDIDNum IN (SELECT * FROM dbo.Split(@FaxDIDNum,','))

Another requirement was to add a date option because the previous query would return every possible record. If the dates were not provided, I wanted the results to only have the previous day's option. I added a begin date and an end date parameter to the stored procedure. If the user omits the parameters, I would calculate a begin date of yesterday and an end date of today. This range would return all items with a date between 12:00 midnight and 12:00 midnight.

CREATE PROCEDURE dbo.sp_GetListOfUniqueIDs 
   (
     @FaxDIDNum VARCHAR(8000), 
     @begin_date smalldatetime = NULL, 
     @end_date smalldatetime = NULL
   )
AS
IF @begin_date IS NULL SET @begin_date = dateadd(dd,0, datediff(dd,0,getdate()))
IF @end_date IS NULL SET @end_date = dateadd(dd,1, datediff(dd,0,getdate()))
SELECT UniqueID
  FROM Documents
WHERE FaxDIDNum IN (SELECT * FROM dbo.Split(@FaxDIDNum,','))
AND CreationTime BETWEEN @begin_date AND @end_date

Now, the developers can call the whole thing as follows:
EXEC sp_GetListOfUniqueIDs '5550001,5550002','2010-06-02','2010-06-03';

I'm excited to have learned this re-usability skill. Maybe now I can find the time to learn how to call a stored procedure from C#.

:-)

Here is the completed query

USE RightFax;
GO
IF OBJECT_ID (N'dbo.Split', N'FN') IS NOT NULL
    DROP FUNCTION dbo.Split;
GO
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
AS       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
 
    SELECT @idx = 1       
        IF len(@String)<1 OR @String IS NULL  RETURN       
 
    while @idx!= 0       
    begin       
        SET @idx = charindex(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
        else       
            SET @slice = @String       
 
        IF(len(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       
 
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       
    end   
RETURN
end      
GO
CREATE PROCEDURE dbo.sp_GetListOfUniqueIDs 
   (
     @FaxDIDNum VARCHAR(8000), 
     @begin_date smalldatetime = NULL, 
     @end_date smalldatetime = NULL
   )
AS
IF @begin_date IS NULL SET @begin_date = dateadd(dd,0, datediff(dd,0,getdate()))
IF @end_date IS NULL SET @end_date = dateadd(dd,1, datediff(dd,0,getdate()))
SELECT UniqueID
  FROM [RightFax].[dbo].[Documents]
WHERE FaxDIDNum IN (SELECT * FROM dbo.Split(@FaxDIDNum,',')) 
AND TermStat = '32'
AND CreationTime BETWEEN @begin_date AND @end_date
ORDER BY UniqueID;
GO
EXEC sp_GetListOfUniqueIDs '5550001,5550002','2010-06-02','2010-06-03';
--EXEC sp_GetListOfUniqueIDs '5550001';
GO
DROP PROCEDURE dbo.sp_GetListOfUniqueIDs;
GO
DROP FUNCTION dbo.split;
GO

Bookmark/Search this post with:

Printer-friendly version

MS SQL Server: Functions, Stored Procedures, Dates, etc.

Post new comment

Technology News

Syndicate