CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Matt Williamson
Matt Williamson

Reputation: 7095

Use current day as default

I'm working on a stored procedure that takes 3 input parameters. The first one is required but the other 2 are dates and are not required. I want the date parameters to default to the current day. Here is what I have so far:

USE [MyDB]
GO

/****** Object:  StoredProcedure [dbo].[p_qIMO_TEST_2]

Example: exec [dbo].[p_qIMO_TEST_2] 'DS5264000001','20090101','20100101'

*/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('qIMO_TEST_2'))
     exec('CREATE PROCEDURE [dbo].[qIMO_TEST_2] AS BEGIN SET NOCOUNT ON; END')
GO

alter PROCEDURE [dbo].[p_qIMO_TEST_2] 
     @i_InstrumentID VARCHAR(15) = NULL,
     @i_DateLow      VARCHAR(15) = '20090101',  
     @i_DateHigh     VARCHAR(15) = '20291231'   
AS
IF @i_InstrumentID IS NULL
BEGIN
    PRINT 'Error: You must specify a valid Instrument ID'
    RETURN
END
     SELECT * FROM
     (
            SELECT 
                 out_interface_id,
                 msg_id, 
                 CAST(xml_msg as XML).value(
                    '(//InstrumentID)[1]','nvarchar(MAX)') AS InstrumentID,
                 msg_type,
                 xml_msg,
                 CAST(xml_msg AS XML) as [Quick_XML], 
                 date_received,
                 status,
                 last_modified,
                 environment,
                 transaction_closed_date
            FROM MyTable
            WHERE msg_type IN ('ABC','DEF') 
            AND date_received >= @i_DateLow
            AND date_received < DATEADD(DAY,1,@i_DateHigh)  -- Need to add 1 to the DateHigh for 
                                                                                                        -- date range criteria to work properly (>= and <)
     ) x
     WHERE (x.InstrumentID = @i_InstrumentID OR x.InstrumentID = NULL)
     ORDER BY date_received DESC
RETURN
GO

Right now, I'm just using an arbitrary range of dates but it takes a long time to return a result. Looking for any suggestions anyone can offer. I'm using MS SQL server 2008 r2.

Upvotes: 0

Views: 40

Answers (1)

Dave C
Dave C

Reputation: 7392

As the comments indicate, check your datatypes, only use VARCHARs if you have to.

Edit for datetime, we convert getdate() to a date type to drop the time value, then back to a datetime type.

alter PROCEDURE [dbo].[p_qIMO_TEST_2] 
     @i_InstrumentID VARCHAR(15) = NULL,
     @i_DateLow      DATETIME = NULL,  
     @i_DateHigh     DATETIME = NULL   
AS

    IF @i_DateLow IS NULL SET @i_DateLow = CONVERT(DATETIME,CONVERT(DATE,GETDATE()))
    IF @i_DateHigh IS NULL SET @i_DateHigh = CONVERT(DATETIME,CONVERT(DATE,GETDATE()))

Upvotes: 1

Related Questions