Stored Procedure Problems

addersting · October 27th, 2003, 09:54 AM

I am having problems with the following stored procedure.
Am trying to pass a parameter "@VariableSwitch" to determine which
code is returned. When passing the "ProgramTeams" this runs fine but with any other entry I get a blank recordset with the column names from the ("vwProgTeams"/ProgramTeams) and blank fields.

It only seems to execute the first SELECT statement nd disregards the parameter.

Is there a better way of doing this ???

'##############
CREATE PROCEDURE spXPAY(@VariableSwitch nvarchar(50),
@DeptID int,@MajorID int,@GradeID int,
@ProgTeamID int,@Current bit, @Active bit,
@PayID int, @YRvalue int, @MNTvalue int,
@StaffID int, @AccountName nvarchar(15),
@SchoolID int,@StatusID int)

AS

if (@VariableSwitch = 'ProgramTeams')
    SELECT vwProgTeams.*
    FROM dbo.vwProgTeams
    WHERE (@SchoolID is null OR intSchoolID = @SchoolID) AND (@ProgTeamID is null OR lngProgTeamIDcnt = @ProgTeamID) AND (blnActive = 1)
else

if (@VariableSwitch = 'Departments')
    SELECT tblDepartments.*
    FROM tblDepartments
    WHERE (@DeptID is null OR lngDeptIDcnt = @DeptID) AND (@MajorID is null OR lngMajorDeptID = @MajorID)
else

if (@VariableSwitch = 'DeptSchools')
    SELECT vwDeptSchools.*
    FROM vwDeptSchools
    WHERE (@DeptID is null OR lngDeptIDcnt = @DeptID)
else

if (@VariableSwitch = 'Grades')
    SELECT tblGrades.*
    FROM tblGrades
    WHERE (@GradeID is null OR lngGradeIDcnt = @GradeID)
else

if (@VariableSwitch = 'LectStaff')
    SELECT vwShowLectStaff.*
    FROM vwShowLectStaff
    WHERE (@ProgTeamID IS NULL) AND (@Current IS NULL) OR (@ProgTeamID IS NULL) AND (blnCurrent = @Current) OR (@Current IS NULL) AND (lngProgTeamID = @ProgTeamID) OR (blnCurrent = @Current)
        AND (lngProgTeamID = @ProgTeamID) AND (@Active is null OR blnActive = @Active)
    ORDER BY txtSurname, txtForename
else

if (@VariableSwitch = 'PayMonths')
    SELECT vwPayMonths.*
    FROM vwPayMonths
    WHERE (@PayID is null OR lngPayIDcnt = @PayID) AND (@YRvalue is null OR YEAR(dtmMonth) = @YRvalue) AND (@MNTvalue is null OR MONTH(dtmMonth) = @MNTvalue)
    ORDER BY dtmMonth
else

if (@VariableSwitch = 'PayrollList')
    SELECT DISTINCT lngDepartmentID, txtDeptName
    FROM vwAdvHours
else

if (@VariableSwitch = 'Staff')
    SELECT vwStaff.*
    FROM vwStaff
    WHERE (@Current is null OR blnCurrent = @Current) AND (@Active is null OR blnActive = @Active) AND (@StaffID is null OR lngStaffIDcnt = @StaffID) AND (@AccountName is null OR txtAccountName = @AccountName)
    ORDER BY txtSurname, txtForename
else

if (@VariableSwitch = 'Status')
    SELECT tblStatus.*
    FROM tblStatus
    WHERE (@StatusID is null OR lngStatusIDcnt = @StatusID)

GO
'##############

Jeff Mason · October 27th, 2003, 11:36 AM

Try creating the procedure using the WITH RECOMPILE option:

Code:

CREATE PROCEDURE spxPAY
<parameters>
WITH RECOMPILE
AS
...

You are probably being hosed becuase the procedure plan is being cached and since the plan differs wildly depending on your parameter (different tables), your procedure needs to be recompiled every time it is run.

This procedure isn't, IMO, a very good design. You would be better served by creating a different procedure for each query, and decide which procedure to call in your client. That way, you can make use of the procedure cache...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

addersting · October 27th, 2003, 11:48 AM

Thanks Jeff,

The original design was multiple procedures but my DB sysadmin wanted to reduce the number of procedures..

I traced part of the problem to testing the procedure in Access, SQL query analyser was being used on another project DB.

The rest is in another procedure that returned teh wrong type..