[Resolved] Using Substring in the Where clause

snufse · January 25th, 2008, 02:51 PM

I have a stored procedure where I would like to use substring in the where clause, if possible.

Code:

CREATE PROCEDURE GetJobList
@PlantId char(3)

Select ......
From ......
WHERE     (dbo.Source.CompanySourceId = 'PROD' and dbo.Batch.ReportDate >= @DateFrom and dbo.Batch.ReportDate <= @DateTo and (if @PlantId <> ' '  then SUBSTRING(dbo.Job.CompanyJobId, 1,3) =  @PlantId))

I get syntax error "Incorrect syntax near 'if, incorrect sysntax near 'then'....

urtrivedi · January 26th, 2008, 01:58 PM

Select ......
From ......
WHERE (dbo.Source.CompanySourceId = 'PROD' and dbo.Batch.ReportDate >= @DateFrom and dbo.Batch.ReportDate <= @DateTo and (case @PlantId <> ' ' then SUBSTRING(dbo.Job.CompanyJobId, 1,3) else '' end = @PlantId))

urt

snufse · January 28th, 2008, 09:22 AM

It worked. Thank you.

Peso · February 1st, 2008, 07:50 PM

And if you want to use any present index, try

Code:

Select    ...
From    ...
WHERE    dbo.Source.CompanySourceId = 'PROD'
    and dbo.Batch.ReportDate >= @DateFrom
    and dbo.Batch.ReportDate <= @DateTo
    and dbo.Job.CompanyJobId like nullif(@PlantId, '') + '%'