Building a Dynamic Query (need some help)

Question

Post reply

Building a Dynamic Query (need some help)

Daniel Negron

Newbie

Points: 1
More actions
April 18, 2006 at 12:16 pm

#365972

I was wondering if anyone could help me with this query. The previous version was generated using @String and then Exec(@String)
I kind of inherited this proc, so I was cleaning it up and came up with the following. But I am not sure how to complete the last part of this section, I thouhgt maybe someone could help me out.
ALTER PROCEDURE SAR_FindRequests
(
@SarId as varchar(50) = NULL,
@SarType as varchar(50) = NULL,
@StatusId as varchar(50) = NULL,
@GeneraResourceId as varchar(50) = NULL,
@ApplicationResourceId as varchar(50) = NULL,
@CreatedFor as varchar(100) = NULL,
@CreatedBy as varchar(50) = NULL,
@AssignedTo as varchar(50) = NULL,
@StartDt as varchar(50) = NULL,
@EndDt as varchar(50) = NULL,
@ApplyDtRangeTo AS VARCHAR(2) = NULL,
@Region as varchar(50) = NULL,
@FacIdentifier as varchar(50) = NULL
)
AS

SELECT sr.SarId, sr.SarTitle, sr.SarType, sr.CreatedBy, sr.CreatedDt, sr.AssignedTo, ss.DisplayText
FROM GM_SAR_REQUESTS as sr
INNER JOIN GM_SAR_STATUS as ss
ON sr.StatusId = ss.StatusId
WHERE sr.Deleted = 0
AND (sr.SarId = @SarId OR @SarId IS NULL)
AND (sr.SarType = @SarType OR @SarType IS NULL )
AND (sr.StatusId = @StatusId OR @StatusId IS NULL)
AND (sr.CreatedBy = @createdby OR @createdby IS NULL)
{Insignificant code cut}
AND (sr.AssignedTo = @AssignedTo OR @AssignedTo IS NULL)
[PROBLEM CODE BELOW]
IF @StartDt IS NOT NULL AND @EndDt IS NOT NULL
BEGIN
IF @ApplyDtRangeTo = 'UD'
BEGIN
  AND (sr.UpdatedDt BETWEEN @StartDt AND @EndDt)
ELSE
  AND (sr.CreatedDt BETWEEN @StartDt AND @EndDt)
END
END
IF @StartDt IS NOT NULL
BEGIN
IF @ApplyDtRangeTo = 'UD'
BEGIN
  AND (sr.UpdatedDt >= @StartDt)
END
ELSE
BEGIN
  AND (sr.CreatedDt >= @StartDt)
END
END
ORDER BY sr.CreatedDt DESC
Any help you guys could give; would greatly be appreciated.
**DAN**

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

sorry don't have time to delve deeper but i would do something like this....

declare a dateColumn variable...

then "set" the name based on @ApplyDtRangeTo..

and then have 2 queries based on "@StartDt & @EndDt"...

declare @ColumnDt varchar(20)
if @ApplyDtRange = 'UD'
    set @ColumnDt = 'sr.UpdatedDt'
else
     set @ColumnDt = 'sr.CreatedDt'
IF @StartDt IS NOT NULL AND @EndDt IS NOT NULL
BEGIN
     process....using AND (@ColumnDt  BETWEEN @StartDt AND @EndDt)
     RETURN
END
IF @StartDt IS NOT NULL
BEGIN
    process...using AND (@ColumnDt >= @StartDt)
END

**ASCII stupid question, get a stupid ANSI !!!**