|
Subject:
|
Stored Procedure Problems
|
|
Posted By:
|
addersting
|
Post Date:
|
10/27/2003 8:54:41 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
'##############
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
10/27/2003 10:36:37 AM
|
Try creating the procedure using the WITH RECOMPILE option:
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
|
|
Reply By:
|
addersting
|
Reply Date:
|
10/27/2003 10:48:07 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..
|
