Using "IN" along with "AND" in WHERE clause

[metsey]

I am trying to create a sql statement that will pull mulitple client codes (from a string input parameter)from a table that have a date later than the input parameter date provided.

However, whenever I try to include the date parameter along with the clientcodes in the WHERE clause, I get an error (declare @DateParameter).

Here is the code - any help would be appreciated.

CREATE PROCEDURE [usp_Profitability]

@sClientCode varchar(255), @Date smalldatetime, @bOpen bit = null output

AS


Exec
(
'SELECT
Enterprise.dbo.InvoiceDetail.ClientCode,

Sum(Enterprise.dbo.InvoiceDetail.GrossPay) as TotGrossPay,
Sum(Enterprise.dbo.InvoiceDetail.WCAdjGrossInv) AS TotAdjGross,
sum(Enterprise.dbo.InvoiceDetail.MgmtBaseFeeAmt)AS AdminInv,
sum(Enterprise.dbo.InvoiceDetail.WCAmtInv)AS WCInv,
sum(Enterprise.dbo.InvoiceDetail.WCDiscountAmtInv) AS WCDiscInv,
sum(Enterprise.dbo.InvoiceDetail.WCBaseCreditInv) AS WCBaseCrd,
sum(Enterprise.dbo.InvoiceDetail.WCSurchargeAmtInv) AS WCSurg,
sum(Enterprise.dbo.InvoiceDetail.WCModRateamtInv) AS WCModInv,
sum(Enterprise.dbo.InvoiceDetail.WCAdjAmtInv) AS WCAdjust,
Sum(Enterprise.dbo.InvoiceDetail.WCAmtCost)AS WCCost,
sum(Enterprise.dbo.InvoiceDetail.SUITaxableInv) AS SUITaxableInc,
sum(Enterprise.dbo.InvoiceDetail.SUITaxAmtInv) AS SUIInv,
sum(Enterprise.dbo.InvoiceDetail.SuiTaxAmtCost)AS SUICost,
sum(Enterprise.dbo.InvoiceDetail.SDITaxAmtInv) AS SDIInv,
sum(Enterprise.dbo.InvoiceDetail.SDITaxAmtCost) AS SDICost,
sum(Enterprise.dbo.InvoiceDetail.SSTaxAmtInv) AS SSInv,
Sum(Enterprise.dbo.InvoiceDetail.SSTaxAmtCost) AS SSCost,
sum(Enterprise.dbo.InvoiceDetail.MedicareTaxAmtInv) AS MedCInv,
sum(Enterprise.dbo.InvoiceDetail.medicareTaxAmtCost) AS MedCCost,
sum(Enterprise.dbo.InvoiceDetail.FUTATaxAmtInv) AS FUTAInv,
sum(Enterprise.dbo.InvoiceDetail.FUTATaxAmtCost) AS FUTACost,
max(Enterprise.dbo.InvoiceDetail.BundleBill) AS Bundle,
min(Enterprise.dbo.InvoiceDetail.InvoiceDate) AS StartDate
FROM
Enterprise.dbo.InvoiceDetail

WHERE Enterprise.dbo.InvoiceDetail.ClientCode in ('+@sClientCode+') AND Enterprise.dbo.InvoiceDetail.InvoiceDate > @Date

GROUP BY Enterprise.dbo.InvoiceDetail.ClientCode
'
)

 

[tlbroadbent]

You need to move @date outside of the quotes so it can be evaluated before executing the SQL statement.

There is another potential problem. Does the parameter @sClientCode contain any single quotes (')? If it does, then change the single quotes you used in the SP to double quotes.

Exec
(
"SELECT
Enterprise.dbo.InvoiceDetail.ClientCode,

Sum(Enterprise.dbo.InvoiceDetail.GrossPay) as TotGrossPay,
.
.
.
FROM
Enterprise.dbo.InvoiceDetail

WHERE Enterprise.dbo.InvoiceDetail.ClientCode IN ("+ @sClientCode + ")
AND Enterprise.dbo.InvoiceDetail.InvoiceDate >" + @Date +
" GROUP BY Enterprise.dbo.InvoiceDetail.ClientCode"
) Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[metsey]

Thank you very much - I am out of the woods!