[Resolved] Using Substring in the Where clause

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

General SQL Server Forums

New to SQL Server Programming

[Resolved] Using Substring in the Where clause

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-25 : 13:52:40

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

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'....

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-25 : 14:04:58

Try this:


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

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-25 : 14:35:01

Sorry, there is no THEN in t-SQL IF statement. Just remove it..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-25 : 14:37:25

Worked. Thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-28 : 01:07:56

Also instead of
SUBSTRING(dbo.Job.CompanyJobId, 1,3) = @PlantId
use
dbo.Job.CompanyJobId like @PlantId+'%'
to make use of index if defined

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources