Build Dynamic var with case issue

[RobHVB6Sql]

The following always seems to evaluation to the first '100' case and doesn't get to the else.
What is wrong with my case? any ideas? :-(

CREATE PROCEDURE spprintTopRFAByVenue
(
	@ServiceVenue	INTEGER,
	@DateFrom	VARCHAR(30),
	@DateTo		VARCHAR(30),
	@TopNNum	INTEGER
)
AS
--
-- 	Work done:
--	1) Extracted TopN number as a parameter
--	2) Adjusted to allow for searching by ALL ServiceVenues (100)
--	Needed to use dynamic variables here to allow for TopN in select
--


-- variables to hold the string to execute
DECLARE @theSQL VARCHAR(200)
DECLARE @theSQL2 VARCHAR(350)

-- build the select statement
SET @theSQL = 
	'SELECT Top ' + CONVERT(varchar, @TopNNum) + ' ' +
	'RFADesc.Description, rfa.Category, rfa.Code, ' +
	CHAR(39) + 'Count' + CHAR(39) + '= count(*) ' +
	'FROM tblRFA rfa, tblGContact c, tblReferenceCode RFADesc '

SET @theSQL2  = CASE @ServiceVenue
	WHEN 100 THEN  -- select ALL Service Venues
		'WHERE rfa.ContactID *= c.ContactID ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateFrom + CHAR(39) + ' ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateTo + CHAR(39) + ' ' +
		'AND RFADesc.ReferenceCode = rfa.Code ' +
		'AND RFADesc.ReferenceFile = rfa.Category ' +
		'GROUP BY RFADesc.Description, rfa.Category, rfa.Code ' + 
		'ORDER BY Count(*) DESC'
	ELSE 
		'WHERE rfa.ContactID *= c.ContactID ' +
		'AND c.ServiceVenue = ' + CONVERT(Varchar, @ServiceVenue) + ' ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateFrom + CHAR(39) + ' ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateTo + CHAR(39) + ' ' +
		'AND RFADesc.ReferenceCode = rfa.Code ' +
		'AND RFADesc.ReferenceFile = rfa.Category ' +
		'GROUP BY RFADesc.Description, rfa.Category, rfa.Code ' + 
		'ORDER BY Count(*) DESC'
	END


-- run the query
EXEC(@theSQL + @theSQL2)

Rob Hasard
(VB6 /SQL 7.0 /CR8.5)

 

[jbenson001]

I just tested the code and worked fine for me. Check that you are passing the correct value before you execute the proc.

Jim

 

[RobHVB6Sql]

Thanks Jim, yep I checked that and all fine.

But if I run the three statement here, I get the same result. As though the service venue (100, 5, 6) is not selected for.

 spprintTopRFAByVenue 100, '01/01/2004', '01/02/2004', 20
 spprintTopRFAByVenue 5, '01/01/2004', '01/02/2004', 20
 spprintTopRFAByVenue 6, '01/01/2004', '01/02/2004', 20

Rob Hasard
(VB6 /SQL 7.0 /CR8.5)

 

[RobHVB6Sql]

UPDATE: I'm unsure why but this works.
Was possibly the *= joins, now using an IF statement.

IF @ServiceVenue = 100 
	SET @theSQL2  = 
		'WHERE rfa.ContactID = c.ContactID ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateFrom + CHAR(39) + ' ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateTo + CHAR(39) + ' ' +
		'AND RFADesc.ReferenceCode = rfa.Code ' +
		'AND RFADesc.ReferenceFile = rfa.Category ' +
		'GROUP BY RFADesc.Description, rfa.Category, rfa.Code ' + 
		'ORDER BY Count(*) DESC'
ELSE
	SET @theSQL2  = 
		'WHERE rfa.ContactID = c.ContactID ' +
 		'AND c.ServiceVenue = ' + CONVERT(Varchar, @ServiceVenue) + ' ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateFrom + CHAR(39) + ' ' +
		'AND c.ContactDate >= ' + CHAR(39) + @DateTo + CHAR(39) + ' ' +
		'AND RFADesc.ReferenceCode = rfa.Code ' +
		'AND RFADesc.ReferenceFile = rfa.Category ' +
		'GROUP BY RFADesc.Description, rfa.Category, rfa.Code ' + 
		'ORDER BY Count(*) DESC'

Rob Hasard
(VB6 /SQL 7.0 /CR8.5)