SQL Case Statement trouble

Question

im not the best when it comes to SQL still learning the ropes. I have a Stored procedure in my SQL server manager 2008.

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[GetSalesBuyers]    Script Date: 03/23/2012  08:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers] 
@Cdisc varchar(255),
@bcs varchar(255), 
@From date, 
@Too date
AS
SELECT i.Acct,
   i.Name, 
   i.Document, 
   i.Part, 
   i.Qty, 
   i.Unit, 
   dbo.NEWPareto.Pareto, 
   i.pg,
   dbo.MyPgTable.PgName,
   i.[DateTime],
   i.BinSeqNo,
   i.cdisc,
   i.bcs

FROM   
OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
                            dbo.iHeads.name,
                            dbo.iLines.Document,
                            dbo.iLines.Part,
                            dbo.iLines.Pg,
                            dbo.iLines.Qty,
                            dbo.iLines.unit,
                            dbo.iHeads.[DateTime], 
                            dbo.iLines.BinSeqNo, 
                            dbo.Customer.cdisc,
                            dbo.Customer.Bcs
                     FROM Autopart.dbo.iheads INNER JOIN   Autopart.dbo.iLines ON 
                     Autopart.dbo.Iheads.document = autopart.dbo.iLines.document
                     INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct 
                     = Autopart.dbo.customer.keycode
                     GROUP By dbo.iHeads.acct,
                            dbo.iHeads.name,
                            dbo.iLines.Document,
                            dbo.iLines.Part,
                            dbo.iLines.Pg,
                            dbo.iLines.Qty,
                            dbo.iLines.unit,
                            dbo.iHeads.[DateTime],
                            dbo.iLines.BinSeqNo,
                            dbo.Customer.cdisc,
                            dbo.Customer.bcs
                      ') i
left JOIN
dbo.NEWPareto
ON 
i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part 
left JOIN
dbo.MyPgTable 
ON  
 i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]

WHERE
 (i.[DateTime] BETWEEN @From AND @Too) 
 AND i.cdisc = @Cdisc 
 AND i.bcs != @bcs
 AND i.pg != '60'
 AND i.pg != '61'
 AND i.pg != '62'

 GROUP BY i.Acct,
   i.Name, 
   i.Document, 
   i.Part, 
   i.Qty, 
   i.Unit, 
   dbo.NEWPareto.Pareto, 
   i.pg,
   dbo.MyPgTable.PgName, 
   i.[DateTime],
   i.BinSeqNo,
   i.cdisc,
   i.bcs

What I need is a condition in the Where clause. particular this row "AND i.bcs != @bcs".

What happens is IF i pass an Empty string to my param i want this row to exist and run in the where clause.

Else IF i dont pass anything (null), i need the row to not exist(not run).

I have played around but all im getting is red lines everywhere when i attempt to create.

Does anyone have an idea? am i close in my methodology? can it be done? or is there a easy mode way that im over looking. Many thanks

Answer 1

Optional parameters are constructed like this:

WHERE (@bcs is null OR i.bcs != @bcs)

If you send null every row is selected; if you send anything else, only non-matching rows are selected. You shouldn't worry about performance because Sql Server is very good with constant expressions, and if it evaluates to one it will be suppressed.

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[GetSalesBuyers]    Script Date: 03/23/2012  08:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers] 
@Cdisc varchar(255),
@bcs varchar(255), 
@From date, 
@Too date
AS
SELECT i.Acct,
   i.Name, 
   i.Document, 
   i.Part, 
   i.Qty, 
   i.Unit, 
   dbo.NEWPareto.Pareto, 
   i.pg,
   dbo.MyPgTable.PgName,
   i.[DateTime],
   i.BinSeqNo,
   i.cdisc,
   i.bcs

FROM   
OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
                            dbo.iHeads.name,
                            dbo.iLines.Document,
                            dbo.iLines.Part,
                            dbo.iLines.Pg,
                            dbo.iLines.Qty,
                            dbo.iLines.unit,
                            dbo.iHeads.[DateTime], 
                            dbo.iLines.BinSeqNo, 
                            dbo.Customer.cdisc,
                            dbo.Customer.Bcs
                     FROM Autopart.dbo.iheads INNER JOIN   Autopart.dbo.iLines ON 
                     Autopart.dbo.Iheads.document = autopart.dbo.iLines.document
                     INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct 
                     = Autopart.dbo.customer.keycode
                     GROUP By dbo.iHeads.acct,
                            dbo.iHeads.name,
                            dbo.iLines.Document,
                            dbo.iLines.Part,
                            dbo.iLines.Pg,
                            dbo.iLines.Qty,
                            dbo.iLines.unit,
                            dbo.iHeads.[DateTime],
                            dbo.iLines.BinSeqNo,
                            dbo.Customer.cdisc,
                            dbo.Customer.bcs
                      ') i
left JOIN
dbo.NEWPareto
ON 
i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part 
left JOIN
dbo.MyPgTable 
ON  
 i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]

WHERE
 (i.[DateTime] BETWEEN @From AND @Too) 
 AND i.cdisc = @Cdisc 
 AND (@bcs is null OR i.bcs != @bcs)
 AND i.pg != '60'
 AND i.pg != '61'
 AND i.pg != '62'

 GROUP BY i.Acct,
   i.Name, 
   i.Document, 
   i.Part, 
   i.Qty, 
   i.Unit, 
   dbo.NEWPareto.Pareto, 
   i.pg,
   dbo.MyPgTable.PgName, 
   i.[DateTime],
   i.BinSeqNo,
   i.cdisc,
   i.bcs

Answer 2

The only way i know of that you can do that (exactly what you want is to use dynamic sql), that being said, its not pretty. You chuck your whole proc in a string variable then build it up as needed. then execute it in the end. I'll update this post with a sample in a bit.

look at this link as to how the syntax looks normally.

Don't judge me I don't code like this normally

but this is the part you're looking for

 IF (ISNULL(@bcs,0) <> 0)
       SET @SQL = @SQL + ' AND i.bcs != '+@bcs + CHAR(13)

Your updated proc.

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[GetSalesBuyers]    Script Date: 03/23/2012  08:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers] 
@Cdisc varchar(255),
@bcs varchar(255), 
@From date, 
@Too date
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = ' SELECT i.Acct, i.Name, i.Document, i.Part, i.Qty, i.Unit, dbo.NEWPareto.Pareto, i.pg,' + CHAR(13)
SET @SQL = @SQL + '   dbo.MyPgTable.PgName,i.[DateTime],i.BinSeqNo,i.cdisc,i.bcs' + CHAR(13)
SET @SQL = @SQL + ' FROM   ' + CHAR(13)
SET @SQL = @SQL + ' OPENQUERY(SACBAUTO, ''SELECT dbo.iHeads.acct, dbo.iHeads.name,dbo.iLines.Document,dbo.iLines.Part, ' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.Pg,dbo.iLines.Qty,dbo.iLines.unit,dbo.iHeads.[DateTime], dbo.iLines.BinSeqNo, ' + CHAR(13)
SET @SQL = @SQL + '                            dbo.Customer.cdisc,dbo.Customer.Bcs ' + CHAR(13)
SET @SQL = @SQL + '                     FROM Autopart.dbo.iheads INNER JOIN   Autopart.dbo.iLines ON ' + CHAR(13)
SET @SQL = @SQL + '                     Autopart.dbo.Iheads.document = autopart.dbo.iLines.document' + CHAR(13)
SET @SQL = @SQL + '                     INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct ' + CHAR(13)
SET @SQL = @SQL + '                     = Autopart.dbo.customer.keycode' + CHAR(13)
SET @SQL = @SQL + '                     GROUP By dbo.iHeads.acct,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iHeads.name,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.Document,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.Part,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.Pg,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.Qty,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.unit,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iHeads.[DateTime],' + CHAR(13)
SET @SQL = @SQL + '                            dbo.iLines.BinSeqNo,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.Customer.cdisc,' + CHAR(13)
SET @SQL = @SQL + '                            dbo.Customer.bcs' + CHAR(13)
SET @SQL = @SQL + '                      '') i' + CHAR(13)
SET @SQL = @SQL + 'left JOIN' + CHAR(13)
SET @SQL = @SQL + 'dbo.NEWPareto' + CHAR(13)
SET @SQL = @SQL + 'ON ' + CHAR(13)
SET @SQL = @SQL + 'i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part ' + CHAR(13)
SET @SQL = @SQL + 'left JOIN' + CHAR(13)
SET @SQL = @SQL + 'dbo.MyPgTable ' + CHAR(13)
SET @SQL = @SQL + 'ON  ' + CHAR(13)
SET @SQL = @SQL + ' i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]' + CHAR(13)
SET @SQL = @SQL + 'WHERE' + CHAR(13)
SET @SQL = @SQL + ' (i.[DateTime] BETWEEN ''+@From+'' AND ''+@Too+'') ' + CHAR(13)
SET @SQL = @SQL + ' AND i.cdisc = '+ @Cdisc  + CHAR(13)


IF (ISNULL(@bcs,0) <> 0)
   SET @SQL = @SQL + ' AND i.bcs != '+@bcs + CHAR(13)


SET @SQL = @SQL + ' AND i.pg != ''60''' + CHAR(13)
SET @SQL = @SQL + ' AND i.pg != ''61''' + CHAR(13)
SET @SQL = @SQL + ' AND i.pg != ''62''' + CHAR(13)

SET @SQL = @SQL + ' GROUP BY i.Acct,' + CHAR(13)
SET @SQL = @SQL + '   i.Name, ' + CHAR(13)
SET @SQL = @SQL + '   i.Document, ' + CHAR(13)
SET @SQL = @SQL + '   i.Part, ' + CHAR(13)
SET @SQL = @SQL + '   i.Qty, ' + CHAR(13)
SET @SQL = @SQL + '   i.Unit, ' + CHAR(13)
SET @SQL = @SQL + '   dbo.NEWPareto.Pareto, ' + CHAR(13)
SET @SQL = @SQL + '   i.pg,' + CHAR(13)
SET @SQL = @SQL + '   dbo.MyPgTable.PgName, ' + CHAR(13)
SET @SQL = @SQL + '   i.[DateTime],' + CHAR(13)
SET @SQL = @SQL + '   i.BinSeqNo,' + CHAR(13)
SET @SQL = @SQL + '   i.cdisc,' + CHAR(13)
SET @SQL = @SQL + '   i.bcs' + CHAR(13)

EXEC(@SQL)