SQL Server Performance

Need help on SQL Stored procedures

Discussion in 'SQL Server 2005 General Developer Questions' started by priya0123, Mar 27, 2007.

  1. priya0123 New Member

    Somebody can help me ... My Requirement says that I have to fetch the data of all territory code
    So I need somebody modify this so that I can fetch all the data without the TerritoryCode

    I dont want to pass the TerritoryCode as variable since I want all the TerritoryCodes data


    Help is appreciated







    CREATE PROCEDURE [dbo].[CH_MP_ST_GetCalendarEvents]
    (
    @TerritoryCode varchar(100),
    @StartDate datetime,
    @EndDate datetime,
    @EventTypevarchar(20),
    @ClientID varchar(20) =''
    )
    AS

    DECLARE @Start INT, @End INT

    SELECT * INTO #TerritoryArray
    FROM dbo.fn_CH_ParseStringToChar(@TerritoryCode, ',')

    SELECT value INTO #ClientArray
    FROM dbo.fn_CH_ParseStringToChar(@ClientID, ',')

    CREATE TABLE #FinalResultTable(
    [ROWID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Name] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [CREATED] [datetime] NULL,
    [SUB_TYPE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EVT_FORMAT_CD] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EventStatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TerritoryCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [X_CLIENT_ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SubEventType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CalculatedTerritoryCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Topic] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [RepLastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [VenueCity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PrimarySpeakerLastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PrimarySpeakerFirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]


    DECLARE @Position int, @Value varchar(50)

    Declare TeeritoryItem Cursor for Select position, value from #TerritoryArray
    Open TeeritoryItem
    Fetch Next From TeeritoryItem Into @Position, @Value
    While @@Fetch_Status = 0
    Begin
    prinT @Value
    IF charindex('*' , @Value, 0) > 0
    BEGIN
    SET @End = charindex('*' , @Value, 0)
    SET @Value = substring(@Value, 0, @End) + '%'
    INSERT INTO #FinalResultTable
    (ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName)
    SELECT ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName
    FROM CH_MP_ST_CalendarEvents
    WHERE SUB_TYPE = 'EVENT' AND EVT_FORMAT_CD = @EventType
    AND (StartDate between @StartDate and @EndDate)
    AND CalculatedTerritoryCode LIKE @Value
    AND X_CLIENT_ID IN (SELECT value FROM #ClientArray)
    END

    ELSE IF @Value = ''
    BEGIN
    INSERT INTO #FinalResultTable
    (ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName)
    SELECT ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName
    FROM CH_MP_ST_CalendarEvents
    WHERE SUB_TYPE = 'EVENT' AND EVT_FORMAT_CD = @EventType
    AND (StartDate between @StartDate and @EndDate)
    AND X_CLIENT_ID IN (SELECT value FROM #ClientArray)
    END
    ELSE IF LOWER(@Value) = 'is null'
    BEGIN
    INSERT INTO #FinalResultTable
    (ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName)
    SELECT ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName
    FROM CH_MP_ST_CalendarEvents
    WHERE SUB_TYPE = 'EVENT' AND EVT_FORMAT_CD = @EventType
    AND (StartDate between @StartDate and @EndDate)
    AND CalculatedTerritoryCode IS NULL
    AND X_CLIENT_ID IN (SELECT value FROM #ClientArray)
    END
    ELSE
    BEGIN
    INSERT INTO #FinalResultTable
    (ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName)
    SELECT ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName
    FROM CH_MP_ST_CalendarEvents
    WHERE SUB_TYPE = 'EVENT' AND EVT_FORMAT_CD = @EventType
    AND (StartDate between @StartDate and @EndDate)
    AND CalculatedTerritoryCode = @Value
    AND X_CLIENT_ID IN (SELECT value FROM #ClientArray)
    END

    Fetch Next From TeeritoryItem Into @Position, @Value
    End
    Close TeeritoryItem
    Deallocate TeeritoryItem

    SELECT * FROM #FinalResultTable ORDER BY StartDate

    DROP TABLE #TerritoryArray
    DROP TABLE #FinalResultTable
    DROP TABLE #ClientArray




    GO
  2. MohammedU New Member

    Try the following...

    CREATE PROCEDURE [dbo].[CH_MP_ST_GetCalendarEvents]
    (
    @StartDate datetime,
    @EndDate datetime,
    @EventType varchar(20),
    @ClientID varchar(20) =''
    )
    AS

    DECLARE @Start INT, @End INT

    SELECT value INTO #ClientArray
    FROM dbo.fn_CH_ParseStringToChar(@ClientID, ',')

    SELECT ROWID,Name,StartDate,EndDate,CREATED,SUB_TYPE,EVT_FORMAT_CD,EventStatus,TerritoryCode,X_CLIENT_ID,SubEventType,CalculatedTerritoryCode,Topic,RepLastName,VenueCity,PrimarySpeakerLastName,PrimarySpeakerFirstName
    FROM CH_MP_ST_CalendarEvents
    WHERE SUB_TYPE = 'EVENT' AND EVT_FORMAT_CD = @EventType
    AND (StartDate between @StartDate and @EndDate)
    AND X_CLIENT_ID IN (SELECT value FROM #ClientArray)


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page