String Parsing Question

Question

Post reply

String Parsing Question

cdun2

SSCertifiable

Points: 7893
More actions
August 17, 2006 at 9:39 am

#153140

Hello,
I have a proc that will serve as the 'data source' of a Crystal Report. This proc will have a single parameter. The user will need the ability to supply one or more values for the parameter, and seperate each value with a comma.
I need an example of how the proc would use the string to supply the criteria for the query in the proc. So far the proc looks like this:
**************************************
CREATE PROCEDURE sp_CDVarianceReporting
@ProdGroup varchar(50)
AS
DECLARE @ProdGroupString varchar(50)
SET @ProdGroupString = @ProdGroup
SELECT
TC,
AcctCode,
CompanyName,
ProdGroup,
[Month],
Txns,
Overall_Rate,
Fuel_Rate,
FuelFee,
SCFee,
Spend/TotalGallons as PPG,
TotalGallons,
Spend
FROM
(
SELECT
  CY.TC,
  CY.AcctCode,
  DA.CompanyName,
  CY.ProdGroup,
  CY.[Month],
  CY.Txns,
  (CY.Fuelfee + CY.SCFee)/Txns as Overall_Rate,
  CY.Fuelfee/CY.Txns as Fuel_Rate,
  CY.FuelFee,
  CY.SCFee,
  CY.DSLGal + CY.RFRGal + CY.OFGal as TotalGallons,
  CY.Spend
FROM
  CYSummaryByMo CY
  INNER JOIN SalesCommissions.dbo.DailyAccountsDownload DA
  ON DA.AcctCode = CY.AcctCode
WHERE
  CY.Txns > 1000
&nbsp AS a
WHERE ProdGroup IN (@ProdGroupString)
*************************************
I've tried to execute the proc as follows;
exec sp_CDVarianceReporting
@ProdGroup = "'gasoline', 'diesel'"
I know that I should get results back, but I don't. I'm guessing that the values need to be parsed out and assigned to variables in order for this to work properly.
If you have any ideas on this, please share.
Thank you for your help!
CSDunn