Parameter for WHERE in Stored Procedure 2

[TooMuchCoffee]

I need to make a stored procedure with the WHERE SQL set to a parameter. I've written the SQL to show the point I'm trying to make but of course I can't do what I need. The part I need help on is where I have the "AND (@INFilter)" what the @INFilter will hold is the following SQL statements:

@INFilter = "(dbo.tblMainAccounts.Company = 'Alpha - Residential' OR dbo.tblMainAccounts.Company = 'Alpha - Commercial' OR dbo.tblMainAccounts.Company = 'Construction - Residential' OR dbo.tblMainAccounts.Company = 'Construction - Commercial')"

I will need to add or subtract more/less dbo.tblMainAccounts.Company varaibles (company names) to the @INFilter (set via VB in code).

Here is the SP SQL part:

USE [Accounting Regions]
GO
/****** Object: StoredProcedure [dbo].[usp_PayrollCommissionBreakout] Script Date: 07/10/2008 10:38:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <AWS>
-- Create date: <June 11, 2008>
-- Description: <Accounting, Payroll Commission Breakout>
-- =============================================
ALTER PROCEDURE [dbo].[usp_PayrollCommissionBreakout]
-- Add the parameters for the stored procedure here
@StartDate datetime,
@EndDate datetime,
@INFilter varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT dbo.vewRegions.StateLong, dbo.tblMainEmployeesRemote.Department, dbo.vewRegions.RegionName,
dbo.tblMainAccounts.Company, SUM(ROUND(dbo.tblMainServices.ServiceHours, 2)) AS SumServiceHours
FROM dbo.tblMainServices INNER JOIN
dbo.tblMainEmployeesRemote ON dbo.tblMainServices.CompletedEmployee = dbo.tblMainEmployeesRemote.EmployeeID INNER JOIN
dbo.tblMainAccounts ON dbo.tblMainServices.AccountKey = dbo.tblMainAccounts.AccountKey INNER JOIN
dbo.vewRegions ON dbo.tblMainAccounts.ServiceZip = dbo.vewRegions.ZIPCode
WHERE (dbo.tblMainServices.DateCompleted BETWEEN CONVERT(DATETIME, @StartDate + '00:00:00', 102) AND CONVERT(DATETIME, @EndDate + '23:59:59', 102))
AND (@INFilter)
AND (LEFT(dbo.tblMainEmployeesRemote.Department, 1) = '3')
GROUP BY dbo.vewRegions.StateLong, dbo.tblMainEmployeesRemote.Department, dbo.vewRegions.RegionName, dbo.tblMainAccounts.Company
ORDER BY dbo.vewRegions.StateLong, dbo.tblMainEmployeesRemote.Department, dbo.vewRegions.RegionName, dbo.tblMainAccounts.Company
END

The error being reported is:
Msg 4145, Level 15, State 1, Procedure usp_PayrollCommissionBreakout, Line 27
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

So how do I make this work? Thanks for your ideas.

 

[SQLSister]

The only way to make that work with that method is with dynamic sQl which I do not recommend especially not for a payroll system. Read about SQL injection attacks to see why.

Personally I would put the items I wanted in the in-filter into a temp table or table variable and then join to the table.

"NOTHING is more important in a database than integrity." ESquared

 

[TooMuchCoffee]

Thank you for the info. I didn't think about adding the filter data to a linked table; good idea.

AWS

 

[PWise]

try
passin the infilter like this
<Alpha - Residential>,<Alpha - Commercial>,<Construction - Residential>

Select .... from tblMainServices 
inner  join xxx
on a=a
....
where (dbo.tblMainServices.DateCompleted BETWEEN CONVERT(DATETIME, @StartDate + '00:00:00', 102) AND CONVERT(DATETIME, @EndDate + '23:59:59', 102))
and charindex('<'+dbo.tblMainAccounts.Company +'>',@infilter )>0

 

[TooMuchCoffee]

pwise: That code change worked like a charm! Thank you. That worked beautifully.

AWS

 

[SQLSister]

Be careful with that solution if you have large tables. It could be a performance killer. I ran that kind of code against one of my large tables and it took 2:42 to return records and my suggested method took 6 seconds to return because my method let it use the index on the column and that method did not.


"NOTHING is more important in a database than integrity." ESquared

 

[TooMuchCoffee]

SQLSister: You bring up a valid point. Thank you for your information.

AWS

 

[SQLSister]

Since you are clearly working in an accounting system, I figured you might have a need to consider performance issues as those can grow to be very large very fast. It is also why the dynamic SQl should never be used not only due to the SQL injection attack issue but due to the fact that it means you must set permissions at the table level instead of the stored procedure level and most accounting systems do not permit that as an internal control (no one on production except the dbas should have direct access to tables to prevent possible fraud).

"NOTHING is more important in a database than integrity." ESquared