Search default values to remove comparing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Search default values to remove comparing

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-09-12 : 03:10:59

I have to do some searching on my web forms.There are numbers of parameters where user can fill info.What i am doing is that if someone not provided the value i put a default value (which i know will not provide will not evaluate)

My stored procedure is

ALTER PROCEDURE [dbo].[uspGetStandAloneDashboardInfo] @MailClass VarChar(3),
@MailShape VarChar(3),
@AssociatedToAppointment VarChar(5),
@InductionDate VarChar(8),
@ConsigneeApptID VarChar(12),
@LocaleKey VarChar(9),
@MailPreparerID VarChar(12),
@ConsigneeContentID VarChar(12),
@LatestTransactionStatus VarChar(75)
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM STANDALONE_CONTENT_INFO sinfo
INNER JOIN HIST_STANDALONE_CONTENT_INFO hsInfo
ON sinfo.SAC_SchedulerContentID = hsInfo.SAC_SchedulerContentID
Where ( @MailClass = '-1-'
OR sinfo.SAC_MailClass = @MailClass )
And ( @LatestTransactionStatus = '-1-'
or hsInfo.SAC_Status = @LatestTransactionStatus )
And ( @MailShape = '-1-'
OR sinfo.SAC_ProcessingCategory = @MailShape )
And ( @ConsigneeContentID = '-1-'
OR hsinfo.SAC_ConsigneeContentID like '%' + @ConsigneeContentID + '%' )
And ( @LocaleKey = '-1-'
OR sinfo.SAC_LocaleKey like '%' + @LocaleKey + '%' )
And ( @MailPreparerID = '-1-'
OR sinfo.SAC_MailPreparerID like '%' + @MailPreparerID + '%' )

END

In it i were thinking that in the where clause
And ( @MailPreparerID = '-1-' OR sinfo.SAC_MailPreparerID like '%' + @MailPreparerID + '%' )

as i provide @MailPreparerID = '-1-' so the condition sinfo.SAC_MailPreparerID like '%' + @MailPreparerID + '%' will not be evaluated

Please correct me and also suggest me any alternative

I have followed technique like http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NonDynamicSearch

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)
[url][/url][url][/url]

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-09-12 : 03:43:22

following is my real stored procedure

USE [AimsDBOfVertis]
GO
/****** Object: StoredProcedure [dbo].[uspGetPartnerAppointmentDashboardInfo] Script Date: 09/12/2012 12:39:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspGetPartnerAppointmentDashboardInfo]
@ConsigneeApptID as varchar(12)
,@LocaleKey as varchar(10)
,@QueryStatus as varchar(15)
,@PartnerID as int
,@RecurringAppointmentStatus as varchar(20)
,@JobID as varchar(8)
,@JobName as varchar(30)
,@UserLabel1 as varchar(40)
,@UserLabel2 as varchar(40)
,@ScheduledApptStartDate as datetime
,@ScheduledApptEndDate as datetime
,@FacilityName as varchar(50)
,@SchedulerID as varchar(12)
,@CRID as varchar(15)
,@CloseOutStatus as varchar(20)
,@TruckArrivalDate as datetime
,@TruckUnloadStart as datetime
,@TruckUnloadEnd as datetime
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET NOCOUNT ON;

WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)

Select distinct HeaderRecord.hdrOriginalFlatFileJobID,
HeaderRecord.hdrJobNameTitleAndIssue,
csmLabelUserInformationLine1,
csmLabelUserInformationLine2,
paqr.ConsigneeApptID,
--HeaderRecord.fkJobID,
PartnerProfile.PartnerID,
PartnerProfile.PartnerName,
paqr.QueryRequestID,
paqr.AppointmentQueryStatus,
paqr.PartnerSchedulerCorpID,
paqr.PartnerSchedulerID,
paqr.PartnerSchedulerCRID,
paqr.LastQueriedAt,
paqr.TrackingID,
paqr.FacilityName,
paqr.FacilityNumber,
paqr.TrailerLength,
paqr.ScheduledAppt,
paqr.ContentCount,
paqr.EffectiveStartDate,
paqr.EffectiveEndDate,
paqr.ApptTime,
paqr.DayFrequency,
paqr.WeekFrequency,
paqr.RecurringAppointmentStatus,
paqr.QueryType,
paqr.PartnerAppointmentQueryStartDate,
paqr.PartnerAppointmentQueryEndDate,
CONVERT(DATETIME2, Closeout_Info.TruckArrival) as FASTTruckArrival,
CONVERT(DATETIME2, Closeout_Info.UnloadStart) as FASTTruckUnloadStart,
CONVERT(DATETIME2, Closeout_Info.UnloadEnd) as FASTTruckUnloadEnd,
Closeout_Info.[Status] as AppointmentCloseoutStatus,
CASE
WHEN (select COUNT( Cast(Y.Barcode as varchar(max)))) > 0 THEN (select COUNT( Cast(Y.Barcode as varchar(max))))
WHEN (select COUNT( Cast(Y.Barcode as varchar(max)))) = 0 THEN NULL
END as ContainerCount
From
PartnerAppointmentQueryResponse paqr Inner Join PartnerProfile ON PartnerProfile.PartnerID = paqr.PartnerID
left outer join
(
SELECT
T.[ConsigneeApptID] as ApptID,
T.QueryRequestID as WithBarcodeQueryRequestID,
x.i.query('data(.)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets//mailxml:USPSContainerInfo/mailxml:UniqueContainerBarcode') AS x(i)
WHERE
PartnerApptQueryResponseType is not null
And ContentCount > 0
) Y ON Y.ApptID = paqr.ConsigneeApptID And paqr.QueryRequestID = Y.WithBarcodeQueryRequestID
LEFT OUTER Join ContainerSummaryRecord on Cast(Y.Barcode as varchar(max)) = ContainerSummaryRecord.csmLabelIMContainerOrIMTrayBarcode and csmIsDeleted = 0 and ContainerSummaryRecord.csmLabelIMContainerOrIMTrayBarcode is not null And Cast(Y.Barcode as varchar(max)) is not null
LEFT OUTER Join HeaderRecord on HeaderRecord.fkJobID = ContainerSummaryRecord.fkJobID and HeaderRecord.hdrHistoryStatus = 'C' and HeaderRecord.hdrIsDeleted = 0
LEFT OUTER JOIN Closeout_Info ON paqr.ConsigneeApptID = Closeout_Info.ConsigneeID and paqr.ConsigneeApptID is not null

where paqr.PartnerID = PartnerProfile.PartnerID
and (@ConsigneeApptID = '-1-' OR paqr.ConsigneeApptID like '%'+ @ConsigneeApptID +'%')
and (@LocaleKey = '-1-' OR paqr.FacilityNumber like '%'+ @LocaleKey +'%')
and (@FacilityName = '-1-' OR paqr.FacilityName like '%'+ @FacilityName +'%')
and (@QueryStatus = '-1-' OR paqr.AppointmentQueryStatus = @QueryStatus)
and (@CRID = '-1-' OR paqr.PartnerSchedulerCRID like '%'+ @CRID +'%')
and (@SchedulerID = '-1-' OR paqr.PartnerSchedulerID like '%'+ @SchedulerID +'%')
and (@RecurringAppointmentStatus = '-1-' OR paqr.RecurringAppointmentStatus = @RecurringAppointmentStatus)
and (@PartnerID IS NULL OR PartnerProfile.PartnerID = @PartnerID)
And (@UserLabel1 ='-1-' or ContainerSummaryRecord.csmLabelUserInformationLine1 like '%'+ @UserLabel1 +'%')
and (@UserLabel2 ='-1-' or ContainerSummaryRecord.csmLabelUserInformationLine2 like '%'+ @UserLabel2 +'%')
and (@JobID ='-1-' or HeaderRecord.hdrOriginalFlatFileJobID like '%'+ @JobID +'%')
and (@JobName ='-1-' or HeaderRecord.hdrJobNameTitleAndIssue like '%'+ @JobName +'%')
and (@CloseOutStatus ='-1-' or Closeout_Info.[Status] like '%'+ @CloseOutStatus +'%')
and (@ScheduledApptStartDate is null or paqr.ScheduledAppt >= @ScheduledApptStartDate )
and (@ScheduledApptEndDate is null or paqr.ScheduledAppt <= @ScheduledApptEndDate )
and (@TruckArrivalDate Is null OR Convert(date,Closeout_Info.TruckArrival) = Convert(date,@TruckArrivalDate))
and (@TruckUnloadStart Is null OR Convert(date,Closeout_Info.UnloadStart) = Convert(date,@TruckUnloadStart))
and (@TruckUnloadEnd Is null OR Convert(date,Closeout_Info.UnloadEnd) = Convert(date,@TruckUnloadEnd))

group by HeaderRecord.hdrOriginalFlatFileJobID,HeaderRecord.hdrJobNameTitleAndIssue, csmLabelUserInformationLine1, csmLabelUserInformationLine2,
paqr.ConsigneeApptID,
-- HeaderRecord.fkJobID,
PartnerProfile.PartnerID,
PartnerProfile.PartnerName,
paqr.QueryRequestID,
paqr.AppointmentQueryStatus,
paqr.PartnerSchedulerCorpID,
paqr.PartnerSchedulerID,
paqr.PartnerSchedulerCRID,
paqr.LastQueriedAt,
paqr.TrackingID,
paqr.FacilityName,
paqr.FacilityNumber,
paqr.TrailerLength,
paqr.ScheduledAppt,
paqr.ContentCount,
paqr.EffectiveStartDate,
paqr.EffectiveEndDate,
paqr.ApptTime,
paqr.DayFrequency,
paqr.WeekFrequency,
paqr.RecurringAppointmentStatus,
paqr.QueryType,
paqr.PartnerAppointmentQueryStartDate,
paqr.PartnerAppointmentQueryEndDate,
Closeout_Info.TruckArrival,Closeout_Info.UnloadStart,Closeout_Info.UnloadEnd,Closeout_Info.[Status]
END

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-12 : 03:55:39

There is no guarantee that SQL will evaluate the first expression first. It reorders it as it sees fit.

http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx

-Chad

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-09-12 : 05:10:40

then any alternative?

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-09-12 : 05:41:54

I am trying dynamic sql
but having problem at
if( @TruckArrivalDate is not null )
begin
set @strwhereClause = @strwhereClause
+ ' and Convert(date,Closeout_Info.TruckArrival) = '
+ Convert(date, @TruckArrivalDate)
end

if( @TruckUnloadStart is not null )
begin
set @strwhereClause = @strwhereClause
+ ' and Convert(date,Closeout_Info.UnloadStart) = '
+ Convert(date, @TruckUnloadStart)
end

if( @TruckUnloadEnd is not null )
begin
set @strwhereClause = @strwhereClause
+ ' and Convert(date,Closeout_Info.UnloadEnd) = '
+ Convert(date, @TruckUnloadEnd)
end

Msg 402, Level 16, State 1, Procedure uspGetPartnerAppointmentDashboardInfoTemp, Line 138
The data types varchar(max) and date are incompatible in the add operator.
Msg 402, Level 16, State 1, Procedure uspGetPartnerAppointmentDashboardInfoTemp, Line 145
The data types varchar(max) and date are incompatible in the add operator.
Msg 402, Level 16, State 1, Procedure uspGetPartnerAppointmentDashboardInfoTemp, Line 152
The data types varchar(max) and date are incompatible in the add operator.

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-12 : 12:41:04

You can't add a datetim into a string, it needs to be varchar.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources