Need help on SQL Stored procedures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need help on SQL Stored procedures

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
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
]]>