Stored Procedure Parameters and IF STATEMENTS 2

[LeonelSanchezJr]

My stored procedure has 5 Input Parameters. Three of these parameters may be null.

My input parameters that can be blank are @StationCode,
@CustCode and @DocType.

If the @StationCode is blank, I will not use the @StationCode in my WHERE clause. I.E. Include all stations.

Same with the @DocType; however, the user may add up to 2 @DocType. Example: "ABC", and "DEF".

How can I do this?

 

[Vancouverite]

Two ways are possible.

(1) Use Dynamic SQL (Build your SQL Query on the fly based on the parameters passed). Many will recommend this.

(2) Modify your WHERE clause to handle these cases, e.g.:

WHERE (@StationCode = MyTable.StationCode OR @StationCode Is Null) AND
(@CustCode = MyTable.CustCode OR @CustCOde Is Null)

However, for including multiple possible DocTypes in one string, you will have to use Dynamic SQL. Either pass multiple possible parameters (@Doctype1 and @DocType2) and use the above style of WHERE clause, or build your SQL query dynamically, e.g.:

DECLARE @SQL varchar(2000)

SET @SQL = 'Select * From MyTable WHERE Field1 = ''' + @Param1 + ''' And Field2 = ''' + Param2 + ''''
IF Not(@CustCode Is Null) SET @SQL = @SQL + ' AND CustCode = ''' + @CustCode + ''''

If Not IsNull(@DocType) Then
BEGIN
If CharIndex(',',@DocType)=0
BEGIN
Set @SQL = @SQL + ' AND DocType = ''' + @DocType + '''
END
ELSE
BEGIN
Set @SQL = @SQL + ' AND (DocType = ''' + Left(@DocType, CharIndex(',',@DocType) - 1) + ''' OR DocType = ''' + Right(@DocType, Len(@DocType) - CharIndex(',',@DocType)) + ''''
END
END

EXEC (@SQL)

 

[LeonelSanchezJr]

There's a few problems with the above two conditions. First, there are a lot of other statements within my stored procedure, so I don't think buidling this Stored Procedure dynamically will work.

Second, in one of my WHERE clauses, I am using the IN statement.

See what I mean?

CREATE PROCEDURE SEL_MissingImages_RPT
@stationcode char(3) = null,
@custcode char(10) = null,
@fromshipdate datetime,
@toshipdate datetime,
@doctype char(18) = null
AS
set nocount on

Select Hawb.cHawbNum
Into #hawboriginrpt
From Hawb (nolock)
Where corigin = @stationcode
And dshipdate Between @fromshipdate And @toshipdate
And hawb.chawbnum Not Like 'ZZ%'

Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
Where hawbdoctypes.cdoctype In ('HAWB','INLND_BL')

Select Hawb.chawbnum
, Hawb.dshipdate
, Hawb.corigin
, Hawb.cdestin
into #MissingHawbs
From Hawb (nolock)
Join #hawboriginrpt (nolock) on hawb.cHawbNum = #hawbOriginRpt.cHawbNum
Where #hawboriginrpt.chawbnum not in (Select #imageoriginrpt.chawbnum From #imageoriginrpt (nolock) )
Order By Hawb.chawbnum

Select #MissingHawbs.cHawbNum
, dShipDate
, cOrigin
, cDestin
, HawbDetail.cCustcode
, HawbDoctypes.cDocType
, Status.cHawbStat
Into #HawbNoImageRpt
From #MissingHawbs
Join HawbDetail (nolock) on #MissingHawbs.cHawbNum = HawbDetail.cHawbNum
Join HawbDocTypes (nolock) on #MissingHawbs.cHawbNum = HawbDocTypes.cHawbNum
Join Status (nolock) on #MissingHawbs.cHawbNum = Status.cHawbNum

Drop Table #HawbOriginRpt
Drop Table #ImageOriginRpt
Drop Table #MissingHawbs
/**********************************************************************************************************/

Select Hawb.cHawbNum
Into #HawbDestinRpt
From Hawb (nolock)
Where cDestin = @stationcode
And dShipDate Between @fromshipdate And @toshipdate
And Hawb.cHawbNum Not Like 'ZZ%'

Select Distinct
Hawbdoctypes.cHawbNum
Into #ImageDestinRpt
From #HawbDestinRpt (nolock)
Join HawbDocTypes (nolock) On #HawbDestinRpt.cHawbNum = HawbDocTypes.cHawbNum
Where HawbDocTypes.cDocType In ('POD')

Select Hawb.chawbnum
, Hawb.dshipdate
, Hawb.corigin
, Hawb.cdestin
into #MissingPods
From Hawb (nolock) Join #HawbDestinRpt (nolock) on Hawb.cHawbNum = #HawbDestinRpt.cHawbNum
Where #HawbDestinRpt.cHawbNum not in (Select #ImageDestinRpt.chawbnum From #ImageDestinRpt (nolock) )
Order By Hawb.chawbnum

Select #MissingPods.cHawbNum
, dShipDate
, cOrigin
, cDestin
, HawbDetail.cCustcode
, HawbDoctypes.cDocType
, Status.cHawbStat
Into #PodNoImageRpt
From #MissingPods
Join HawbDetail (nolock) on #MissingPods.cHawbNum = HawbDetail.cHawbNum
Join HawbDocTypes (nolock) on #MissingPods.cHawbNum = HawbDocTypes.cHawbNum
Join Status (nolock) on #MissingPods.cHawbNum = Status.cHawbNum

Drop Table #ImageDestinRpt
Drop Table #HawbDestinRpt
Drop Table #MissingPods

Select * From #HawbNoImageRpt
union all select * from #podnoimagerpt order by chawbnum
GO

 

[rajeevnandanmishra]

dear,
I thought that you can use the following solution.

I had made change in one of your select statement the same can be made throughout.

-----
Select Hawb.cHawbNum
Into #HawbDestinRpt
From Hawb (nolock)
Where cDestin = case @stationcode is null
then cDestin
else @stationcode end
And dShipDate Between @fromshipdate And @toshipdate
And Hawb.cHawbNum Not Like 'ZZ%'
---------

and for @doctype you can use two new variables and continue using your IN clause as
----------------
declare @doc1 char(18), @doc2 char(18)
if charindex(',',@doctype)<>0
begin
select @doc1=substring(@doctype,1,charindex(',',@doctype)-1)
select @doc2=substring(@doctype,charindex(',',@doctype)+1,len(@doctype)-charindex(',',@doctype))
end
else
begin
select @doc1=@doctype
select @doc2=@doctype
end

sql statement
WHERE HawbDocTypes.cDocType In case @doctype is null
then (HawbDocTypes.cDocType)
else (@doc1,@doc2) end
----------------------------


Hope this will help you in finding your solution.

 

[LeonelSanchezJr]

The above code has some syntax errors. Can anyone check the above code for me?

 

[rajeevnandanmishra]

Hi Leonel,
I had checked the code and it works.
Please can you send me your code. May be i can do some help by seeing it.

 

[LeonelSanchezJr]

Sure no problem. Is this where you wanted me to send it?

I do appreciate all your help. You are the greatest! B-)

CREATE PROCEDURE SEL_MissingImages_RPT2
@stationcode char(3) = null,
@custcode char(10) = null,
@fromshipdate datetime,
@toshipdate datetime,
@doctype char(18) = null
AS
set nocount on

Select Hawb.cHawbNum
Into #hawboriginrpt
From Hawb (nolock)
Where corigin = Case When @stationcode Is Null
Then cDestin
Else @stationcode
End
And dshipdate Between @fromshipdate And @toshipdate
And hawb.chawbnum Not Like 'ZZ%'

Declare @Doc1 char(18), @Doc2 char(18)

If CharIndex(',',@Doctype) <> 0
Begin
Select @Doc1=Substring(@Doctype,1,CharIndex
(',',@Doctype)-1)
Select @Doc2 = Substring(@Doctype,1,CharIndex
(',',@Doctype+1,Len(@Doctype) - Charindex
(',',@Doctype) ) )
End
Else
Select @Doc1 = @Doctype
Select @Doc2 = @Doctype


Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
Where HawbDocTypes.cDocType In Case @Doctype Is Null
Then (HawbDocTypes.cDocType)
Else (@Doc1,@Doc2) End

Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum =
hawbdoctypes.chawbnum
Where hawbdoctypes.cdoctype In ('HAWB','INLND_BL')

Select Hawb.chawbnum
, Hawb.dshipdate
, Hawb.corigin
, Hawb.cdestin
into #MissingHawbs
From Hawb (nolock)
Join #hawboriginrpt (nolock) on hawb.cHawbNum = #hawbOriginRpt.cHawbNum
Where #hawboriginrpt.chawbnum not in (Select #imageoriginrpt.chawbnum From #imageoriginrpt (nolock) )
Order By Hawb.chawbnum

Select #MissingHawbs.cHawbNum
, dShipDate
, cOrigin
, cDestin
, HawbDetail.cCustcode
, HawbDoctypes.cDocType
, Status.cHawbStat
Into #HawbNoImageRpt
From #MissingHawbs
Join HawbDetail (nolock) on #MissingHawbs.cHawbNum = HawbDetail.cHawbNum
Join HawbDocTypes (nolock) on #MissingHawbs.cHawbNum = HawbDocTypes.cHawbNum
Join Status (nolock) on #MissingHawbs.cHawbNum = Status.cHawbNum

Drop Table #HawbOriginRpt
Drop Table #ImageOriginRpt
Drop Table #MissingHawbs


Select Hawb.cHawbNum
Into #HawbDestinRpt
From Hawb (nolock)
Where cDestin = @stationcode
And dShipDate Between @fromshipdate And @toshipdate
And Hawb.cHawbNum Not Like 'ZZ%'

Select Distinct
Hawbdoctypes.cHawbNum
Into #ImageDestinRpt
From #HawbDestinRpt (nolock)
Join HawbDocTypes (nolock) On #HawbDestinRpt.cHawbNum = HawbDocTypes.cHawbNum
Where HawbDocTypes.cDocType In ('POD')

Select Hawb.chawbnum
, Hawb.dshipdate
, Hawb.corigin
, Hawb.cdestin
into #MissingPods
From Hawb (nolock)
Join #HawbDestinRpt (nolock) on Hawb.cHawbNum = #HawbDestinRpt.cHawbNum
Where #HawbDestinRpt.cHawbNum not in (Select #ImageDestinRpt.chawbnum From #ImageDestinRpt (nolock) )
Order By Hawb.chawbnum

Select #MissingPods.cHawbNum
, dShipDate
, cOrigin
, cDestin
, HawbDetail.cCustcode
, HawbDoctypes.cDocType
, Status.cHawbStat
Into #PodNoImageRpt
From #MissingPods
Join HawbDetail (nolock) on #MissingPods.cHawbNum = HawbDetail.cHawbNum
Join HawbDocTypes (nolock) on #MissingPods.cHawbNum = HawbDocTypes.cHawbNum
Join Status (nolock) on #MissingPods.cHawbNum = Status.cHawbNum

Drop Table #ImageDestinRpt
Drop Table #HawbDestinRpt
Drop Table #MissingPods

Select * From #HawbNoImageRpt
union all select * from #podnoimagerpt order by chawbnum

GO

 

[rajeevnandanmishra]

Here i am copying back your code:
May be it is a problem because of commands breaks
Now try it. If still it gives an error please send me the code snippet for the actual line causing the problem.

CREATE PROCEDURE SEL_MissingImages_RPT2
@stationcode char(3) = null,
@custcode char(10) = null,
@fromshipdate datetime,
@toshipdate datetime,
@doctype char(18) = null
AS
set nocount on

Select Hawb.cHawbNum
Into #hawboriginrpt
From Hawb (nolock)
Where corigin = Case When @stationcode Is Null
Then corigin
Else @stationcode
End
And dshipdate Between @fromshipdate And @toshipdate
And hawb.chawbnum Not Like 'ZZ%'

Declare @Doc1 char(18), @Doc2 char(18)

If CharIndex(',',@Doctype) <> 0
Begin
Select @Doc1=Substring(@Doctype,1,CharIndex(',',@Doctype)-1)
Select @Doc2 = Substring(@Doctype,1,CharIndex(',',@Doctype+1,Len(@Doctype) - Charindex(',',@Doctype) ) )
End
Else
Begin
Select @Doc1 = @Doctype
Select @Doc2 = @Doctype
End


Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
Where HawbDocTypes.cDocType In Case @Doctype Is Null
Then (HawbDocTypes.cDocType)
Else (@Doc1,@Doc2) End

Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
Where hawbdoctypes.cdoctype In ('HAWB','INLND_BL')

Select Hawb.chawbnum
, Hawb.dshipdate
, Hawb.corigin
, Hawb.cdestin
into #MissingHawbs
From Hawb (nolock)
Join #hawboriginrpt (nolock) on hawb.cHawbNum = #hawbOriginRpt.cHawbNum
Where #hawboriginrpt.chawbnum not in (Select #imageoriginrpt.chawbnum From #imageoriginrpt (nolock) )
Order By Hawb.chawbnum

Select #MissingHawbs.cHawbNum
, dShipDate
, cOrigin
, cDestin
, HawbDetail.cCustcode
, HawbDoctypes.cDocType
, Status.cHawbStat
Into #HawbNoImageRpt
From #MissingHawbs
Join HawbDetail (nolock) on #MissingHawbs.cHawbNum = HawbDetail.cHawbNum
Join HawbDocTypes (nolock) on #MissingHawbs.cHawbNum = HawbDocTypes.cHawbNum
Join Status (nolock) on #MissingHawbs.cHawbNum = Status.cHawbNum

Drop Table #HawbOriginRpt
Drop Table #ImageOriginRpt
Drop Table #MissingHawbs


Select Hawb.cHawbNum
Into #HawbDestinRpt
From Hawb (nolock)
Where cDestin = @stationcode
And dShipDate Between @fromshipdate And @toshipdate
And Hawb.cHawbNum Not Like 'ZZ%'

Select Distinct
Hawbdoctypes.cHawbNum
Into #ImageDestinRpt
From #HawbDestinRpt (nolock)
Join HawbDocTypes (nolock) On #HawbDestinRpt.cHawbNum = HawbDocTypes.cHawbNum
Where HawbDocTypes.cDocType In ('POD')

Select Hawb.chawbnum
, Hawb.dshipdate
, Hawb.corigin
, Hawb.cdestin
into #MissingPods
From Hawb (nolock)
Join #HawbDestinRpt (nolock) on Hawb.cHawbNum = #HawbDestinRpt.cHawbNum
Where #HawbDestinRpt.cHawbNum not in (Select #ImageDestinRpt.chawbnum From #ImageDestinRpt (nolock) )
Order By Hawb.chawbnum

Select #MissingPods.cHawbNum
, dShipDate
, cOrigin
, cDestin
, HawbDetail.cCustcode
, HawbDoctypes.cDocType
, Status.cHawbStat
Into #PodNoImageRpt
From #MissingPods
Join HawbDetail (nolock) on #MissingPods.cHawbNum = HawbDetail.cHawbNum
Join HawbDocTypes (nolock) on #MissingPods.cHawbNum = HawbDocTypes.cHawbNum
Join Status (nolock) on #MissingPods.cHawbNum = Status.cHawbNum

Drop Table #ImageDestinRpt
Drop Table #HawbDestinRpt
Drop Table #MissingPods

Select * From #HawbNoImageRpt
union all select * from #podnoimagerpt order by chawbnum

GO

 

[LeonelSanchezJr]

I get the following error with these lines of code: :-(

Incorrect Syntax near the Keyword CASE.

Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
Where HawbDocTypes.cDocType In Case @Doctype Is Null
Then (HawbDocTypes.cDocType)
Else (@Doc1,@Doc2) End

 

[rajeevnandanmishra]

Oh Sorry!
The syntax of case command is
CASE WHEN <condition> THEN <value> ELSE <value> END

So please add WHEN in the above code as follows:

---------
Case WHEN @Doctype Is Null
Then (HawbDocTypes.cDocType)
Else (@Doc1,@Doc2) End
--------------------------
Sorry for the wrong syntax.

 

[LeonelSanchezJr]

I added the WHEN part. Then I need to add the parenthesis after the In clause and after the End clause.

However, I am now getting an error regarding an incorrect syntax near the &quot;,&quot; comma which is referring to (@Doc1,@Doc2).

Thank you so much for &quot;hanging in there with me&quot;.

Select Distinct
hawbdoctypes.chawbnum
Into #imageoriginrpt
From #hawboriginrpt (nolock)
Join hawbdoctypes (nolock) On #hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
Where HawbDocTypes.cDocType In (Case When @Doctype Is Null
Then (HawbDocTypes.cDocType)
Else (@Doc1,@Doc2) End)

 

[rajeevnandanmishra]

I am very sorry Leonel, I am unable to find the solution as per my syntax. Now i can help you in this tomorrow.
But i think you have to use one of the following methods :
1) IF @doctype is null
begin
sql statements
end
ELSE
begin
sql statements
end

2) Building dyanamic SQLs.

I regret, i had wasted so much of your valuable time. I am really sorry.

 

[LeonelSanchezJr]

No problem, I'm doing much better now then I was before you helped me. You've been a real life-saver. Thank you for all your efforts. You definitely got me going in the right direction and I've got it working; I just have to add it to the other parameters now. Thanks again.