Stored Procedure Help Needed - PLEASE....

[Rock6431]

This is driving me crazy... Let me try to explain.

I am trying to convert the following CF/SQL over into a SQL only Stored Procedure. This CF/SQL query selects the 9 different fields from the Data table and then dependent upon whether Market, RepCenter, RepTDL, RepLname were supplied or if they were null, it would add the "AND Market = Form.Market" to the CF/SQL statement. Therefore, if none of the 4 fields were populated, it would return all records on that report date. BUT if one or more fields were filled out, it would filter the query by adding on those conditions. I think this is where I am having problems in converting this to a stored procedure. See below....


<CFquery name=&quot;GetData&quot; datasource=&quot;LOS&quot;>
SELECT Exceptions, Status, ExceptionDate, Market,
ReportDate, RepCenter, RepTDL, RepLname, Age
FROM Data
Where (ReportDate = '#Form.ReportDate#')
<CFIF (form.Market neq &quot;&quot> AND (Market = '#Form.Market#')</CFIF>
<CFIF (form.RepCenter neq &quot;&quot> AND (RepCenter = '#Form.RepCenter#')</CFIF>
<CFIF (form.RepTDL neq &quot;&quot> AND (RepTDL = '#Form.RepTDL#')</CFIF>
<CFIF (form.RepLname neq &quot;&quot> AND (RepLname = '#Form.RepLname#')</CFIF>
Order BY Exceptions, Status
</CFQUERY>


So, far I have got it down to this...


CREATE PROCEDURE [dbo].[GetMyData_sp]
@ReportDate char (10),
@Market char (50),
@RepCenter char (50),
@RepTDL char (10),
@RepLname char (30)
AS
SELECT Exceptions, Status, ExceptionDate, Market, ReportDate, RepCenter, RepTDL, RepLname, Age
FROM [dbo].Data
Where ReportDate = @ReportDate
IF @Market <> NULL AND (Market = @Market)
IF @RepCenter <> NULL AND (RepCenter = @RepCenter)
IF @RepTDL <> NULL AND (RepTDL = @RepTDL)
IF @RepLname <> NULL AND (RepLname = @RepLname)
GROUP BY Exceptions, Status
GO

The SQL Stored Procedure interface continues to state that there is an error close to GROUP. When I remove it completely, I get Error close to &quot&quot; in line 17 which is the line immediately above that one. I remove the () and it still errors on that line... What gives???

Can someone help point me in the right direction?

Rock6431

 

[nealvenables]

Try building the SQL statement dynamically before executing it, e.g.

CREATE PROCEDURE [dbo].[GetMyData_sp]
@ReportDate char (10),
@Market char (50),
@RepCenter char (50),
@RepTDL char (10),
@RepLname char (30)
AS
DELCLARE @SQL varchar(1000)

SELECT @SQL = 'SELECT Exceptions, Status, ExceptionDate, Market, ' +
' ReportDate, RepCenter, RepTDL, RepLname, Age '
'FROM [dbo].Data
'Where (ReportDate = ''' + @ReportDate + ''') '
IF @Market <> NULL
BEGIN
SELECT @SQL = @SQL + ' AND (Market = ''' + @Market + ''') '
END
IF @RepCentre <> NULL
BEGIN
SELECT @SQL + @SQL + 'AND (RepCenter = ''' + @RepCenter + ''') '
END
IF @RepTDL <> NULL
BEGIN
SELECT @SQL = @SQL + 'AND (RepTDL = ''' + @RepTDL + ''') '
END
IF @RepLname <> NULL
BEGIN
SELECT @SQL = @SQL + 'AND (RepLname = ''' + @RepLname + ''') '
END
SELECT @SQL = @SQL + 'GROUP BY Exceptions, Status '

EXEC (@SQL)