Optional Parameters - SQL Server Forums

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

Optional Parameters

Author

Topic

jauner
Starting Member

19 Posts

Posted - 2009-11-09 : 11:30:57

I have the following SQL:


SELECT
	jobtran.Trans_Date	     
	, job.job + '-' + cast(job.suffix as varchar) as Job
	, job.item
	, jobtran.wc
	, (SELECT description FROM WC WHERE wc = jobtran.wc) AS WC_Description
	, jobtran.Reason_Code
	, reason.Description
	, jobtran.Qty_Scrapped
	, item.Unit_Cost     
FROM jobtran
	inner join job on jobtran.job = job.job and jobtran.suffix = job.suffix
	inner join item on job.item = item.item
	left outer join reason on jobtran.reason_code = reason.reason_code and reason_class = 'MFG SCRAP'
WHERE
     (jobtran.trans_date BETWEEN @PStartingJobDate AND @PEndingJobDate) 
     AND jobtran.qty_scrapped > 0 
     AND CHARINDEX(job.stat, @PJobStatus) <> 0
ORDER BY jobtran.trans_date, job.job, job.suffix

I would like to add the following to the where clause:

jobtran.wc between @wcstart and @wcend

But if @wcstart and @wcend are both null I want it to disregard this part of the where clause. Is there a way to do that?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 11:32:59

can 1 be null and the other not?

If so what do you want to do then?

EDIT: Also you do know that it will between datetime WITH time

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 11:39:42

do you mean this?

and ((jobtran.wc >= @wcstart or @wcstart is null )
and (jobtran.wc<dateadd(dd,1,@wcend) or @ wcend is null) )

jauner
Starting Member

19 Posts

Posted - 2009-11-09 : 14:13:25

I figured it out. Since it is in a stored procedure I have set the @ variable to @wcstart = ISNull(@wcstart, dbo.lowcharacter()) and the same for @wcend except using dbo.highcharacter()

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 14:16:05

shhhhhiivver

Is the datetime column really varchar?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 12:53:03

what does dbo.lowcharacter() etc do?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-10 : 13:44:56

quote:
Originally posted by visakh16

what does dbo.lowcharacter() etc do?

Reminds me of a line out of Unforgiven

http://www.imdb.com/title/tt0105695/quotes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources