April 23, 2002 at 11:47 am
Is there any way to dynamically declare a cursor? I need to declare a cursor with a select statement passed in as a parameter to the stored proc.
April 23, 2002 at 12:41 pm
Not without writing the entire process dynamically in a string and executing so it all runs in the same user context.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 23, 2002 at 1:13 pm
What is a reason for passing a complete statement as a parameter, what are you trying to accomplish?
April 23, 2002 at 1:54 pm
This stored proc will be called by a VB program with a variable number and type of criteria to select IDs. Basically, given some criteria with which to find a list of IDs, the stored proc needs to do so mething for each item in the list. This is further complicated by the fact that the critera may select on several different tables that join through many to many relationships to build the list.
I have come up with a solution, however. Basically, I create a temporary table, use a dynamic insert statement to handle the input criteria, and then create a cursor based on the temp table. The actual procedure is more complicated, but that's the general idea.
April 23, 2002 at 2:40 pm
Personally using a cursor in VB app on the server is bad for performance especially if you have a lot of clients. However if you can post what you have and give us some details on the tables (the DDL is best) we may be able to see a better solution or one you haven't thought of. Also if you take and do a dynamic SQL set like I described you will have to give access to the underlying tables which sorta defeats one of the major purposes of stored procedures and an exectution plan will not be saved, which is the number one reason for stored procedures.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 12:36 pm
It's not exactly doing server cursors in vb, just cursors in the stored proc. Here's what I've got:
CREATE PROCEDURE calc_PSRSummaryMixed
@FY_CD varchar(6),
@EMPL_IDCriteria varchar(8000),
@PROJ_IDCriteria varchar(8000),
@IncludePerforming bit
AS
SET NOCOUNT ON
DECLARE @UserProfileID int
DECLARE @EMPL_IDList varchar(1024)
DECLARE @EMPL_ID varchar(12)
CREATE TABLE #AllUsers
(
EMPL_ID varchar(12),
)
IF NOT @EMPL_IDCriteria IS NULL
BEGIN
CREATE TABLE #Users
(
EMPL_ID varchar(12),
EMPL_IDList varchar(1024)
)
EXEC('INSERT INTO #Users SELECT EMPL_ID, EMPL_IDList FROM gen_Users WHERE ' + @EMPL_IDCriteria)
DECLARE userCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT * FROM #Users
FOR READ ONLY
open userCursor
fetch next from userCursor into @EMPL_ID, @EMPL_IDList
while @@Fetch_Status = 0
begin
IF NOT @EMPL_IDList IS NULL
BEGIN
SET @EMPL_IDList = REPLACE(REPLACE(@EMPL_IDList, ', ', ','), ',', ''',''')
EXEC('INSERT INTO #AllUsers SELECT EMPL_ID FROM gen_Users WHERE EMPL_ID IN (''' + @EMPL_IDList + ''')')
END
INSERT INTO #AllUsers (EMPL_ID) VALUES (@EMPL_ID)
fetch next from userCursor into @EMPL_ID, @EMPL_IDList
end
close userCursor
deallocate userCursor
END
DECLARE @UserProfileTableSELECT varchar(8000)
CREATE TABLE #UserProfiles
(
UserProfileID int
)
IF NOT @EMPL_IDCriteria IS NULL
BEGIN
EXEC('INSERT INTO #UserProfiles SELECT DISTINCT UserProfileID FROM gen_Users WHERE ' + @EMPL_IDCriteria)
DECLARE userProfileCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT * FROM #UserProfiles
FOR READ ONLY
OPEN userProfileCursor
fetch next from userProfileCursor into @UserProfileID
while @@Fetch_Status = 0
begin
if (@UserProfileTableSELECT IS NULL)
set @UserProfileTableSELECT = 'SELECT PROJ_ID FROM gen_Projects_' + @UserProfileID
else
set @UserProfileTableSELECT = @UserProfileTableSELECT + ' UNION SELECT PROJ_ID FROM gen_Projects_' + @UserProfileID
fetch next from userProfileCursor INTO @UserProfileID
end
close userProfileCursor
deallocate userProfileCursor
END
CREATE TABLE #Projects
(
PROJ_ID VARCHAR(30)
)
CREATE UNIQUE INDEX proj_id_index ON #Projects (PROJ_ID)
IF @PROJ_IDCriteria IS NULL
SET @PROJ_IDCriteria = ''
ELSE
SET @PROJ_IDCriteria = ' UNION SELECT PROJ_ID FROM PROJ WHERE ' + @PROJ_IDCriteria
DECLARE @DoUnion varchar(7)
IF @UserProfileTableSELECT IS NULL
SET @DoUnion = ''
ELSE
SET @DoUnion = ' UNION '
IF @IncludePerforming = 0
exec('INSERT INTO #Projects ' + @UserProfileTableSELECT + @DoUnion +
' SELECT PROJ_ID FROM gen_User2Project INNER JOIN #AllUsers ON (gen_User2Project.EMPL_ID = #AllUsers.EMPL_ID) WHERE IsOwner=1' + @PROJ_IDCriteria)
ELSE
exec('INSERT INTO #Projects ' + @UserProfileTableSELECT + @DoUnion +
' SELECT PROJ_ID FROM gen_User2Project INNER JOIN #AllUsers ON (gen_User2Project.EMPL_ID = #AllUsers.EMPL_ID)' + @PROJ_IDCriteria)
April 24, 2002 at 12:37 pm
Oops. The sp was too long to fit in a single message<g>... Here's the rest:
SELECT tblPSRReport.PD_NO, SUM(PTD_INCUR_AMT) AS PTD_INCUR_AMT, SUM( YTD_INCUR_AMT) AS YTD_INCUR_AMT, tblPD_NO2Month.Short AS ShortMonthName, @FY_CD AS FY_CD FROM tblPsrReport
INNER JOIN #Projects ON (tblPSRReport.PROJ_ID = #Projects.PROJ_ID)
INNER JOIN tblPD_NO2Month ON (tblPsrReport.PD_NO = tblPD_NO2Month.PD_NO)
WHERE S_ACCT_FUNC_DC IS NULL AND POOL_NAME='' AND FY_CD=@FY_CD
GROUP BY tblPSRReport.PD_NO, tblPD_NO2Month.Short
DROP TABLE #Projects
GO
BTW, is there any way to access the resultset of a stored proc from the calling stored proc? I'd like to break out the PROJ_ID calculation to a seperate stored proc, because I have about 10 sps that are almost exactly the same as this one, except they've got different select statements at the end...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply