dynamic SELECT statement in stored procedure 2

[varocho]

Using: Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)

I'm creating a stored procedure for a report Web page which allows the user to specify different criteria. I need it to be able to dynamically build parts of the WHERE clause. Here's (some of) what I have so far:

CREATE PROC sp_survey_report
@business char(10),
@sub_business char(10),
@engagement_type char(8),
@full_part_time_ind char(3),
@comp_type char(3)
AS
IF @business = "ALL" SELECT @business = "%"
IF @sub_business = "ALL" SELECT @sub_business = "%"
IF @engagement_type = "ALL" SELECT @engagement_type = "%"
IF @full_part_time_ind = "ALL" SELECT @full_part_time_ind = "%"
IF @comp_type = "ALL" SELECT @comp_type = "%"

SELECT COUNT(*)
FROM t_survey, t_survey_data
WHERE t_survey.survey_id = t_survey_data.survey_id
AND question_id = "1" AND response_value = "01"
AND business like @business
AND sub_business like @sub_business
AND engagement_type like @engagement_type
AND full_part_time_ind like SUBSTRING(@full_part_time_ind, 1, 1)
AND comp_type like SUBSTRING(@comp_type, 1, 1)
...;

('full_part_time_ind' and 'comp_type' are 1-character columns in t_survey)

When I call the SP like this

exec sp_survey_report "SUPPLY", "", "NHR", "F", "E";

I get the correct counts, but when I call the SP like this

exec sp_survey_report "ALL", "ALL", "ALL", "ALL", "ALL";

I get incorrect results. What gives?

Alternatively, I think the following is possible:

CREATE PROCEDURE myProc
blah
blah
AS
DECLARE @command varchar(255)

SELECT @command = "SELECT COUNT(*) FROM blah blah..."

IF some_condition @command = @command + @something

EXEC @command

;

The question is, how can I get the COUNT returned from the executed SELECT statement??

 

[Tim1]

Hiya,

1. The way to select all on your stored procedure is not to pass the word "ALL", that is being included in your where clause, and will only return rows that match (i.e. where business and sub-business are ALL etc). I think that you will need to modify your where clause to use the "LIKE" command, then you can just pass a % into the stored proc, and that should return all rows.

2. You will need to declare a variable to pass a result back out of the stored procedure and then store to count to that, or:

DECLARE @count INT

SELECT @count = COUNT(*)
FROM <table>

PRINT &quot;Count of <table> is %1!&quot;,@count

 

[varocho]

Tim,

Thanks for responding. As for your first answer, I am using the LIKE operator in the WHERE clause, along with the '%' wildcard character in the event that 'ALL' is passed to the procedure (that's why I have the IF statements). As for your second answer, I need to dynamically build the WHERE clause, a simple SELECT won't do what I need.

 

[mgid]

If possible, I think it's easier to build the correct sql string before sending it to the DB. Maybe the following will work with strCriteria being the WHERE argument:

IF parameter1 condition...
THEN strCriteria = strCriteria & parameter1 & &quot; AND &quot;
IF parameter2 condition...
THEN strCriteria = strCriteria & parameter2 & &quot; AND &quot;
etc.
etc.
strCriteriaFinal = Left(strCriteria, Len(strCriteria)-5)

sqltext = &quot;SELECT blah, blah, FROM blah, blah WHERE &quot;& strCriteria

 

[tlbroadbent]

You can build the dynamic SQL in the SP as you've started and then use Execute or sp_executesql to execute the SQL string. You must also make sure you delimit the character or text fields with quotes.

[tt]DECLARE @sql varchar(1024)

Select @sql=
&quot;SELECT COUNT(*)
FROM t_survey s Inner Join t_survey_data d
ON s.survey_id=d.survey_id
WHERE question_id = '1'
AND response_value = '01'
AND business like '&quot; + @business + &quot;'
AND sub_business like '&quot; + @sub_business + &quot;'
AND engagement_type like '&quot; + @engagement_type + &quot;'
AND full_part_time_ind like '&quot; + SUBSTRING(@full_part_time_ind, 1, 1) + &quot;'
AND comp_type like '&quot; + SUBSTRING(@comp_type, 1, 1) + &quot;'&quot;
...;

Execute(@sql)[/tt] Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot