Can you query against a CURSOR?
-
I haven't had too much extra time at the moment to work on this process further.....
Sreejith I looked at the last post of yours, and the example you left for me to try looks good but it will give me two records if it meets the criteria and I need one or the other...
For example
199 6 100 ga Allied 2500 BON film I 100ALI 7 6
199 7 100 ga 2500 BON film A 100ALI 7 6
I would want to see only the A status record...not both the I and A status records...
Here is the current stored proc that does the work...it still uses a cursor and runs in about 30 seconds...and it runs dumps to a local table...and then uses a DTS to push the information to a seperate table on a different server.
ALTER PROCEDURE bp_CARDs_GetMTLInformation
AS
BEGIN
BEGIN
DECLARE @IN_MatNum INT,
@IN_VerNumMat INT,
@IN_MatDesc VARCHAR(60),
@IN_SpecSta VARCHAR(1),
@IN_OldMatID VARCHAR(12),
@IN_MatTypeCode SMALLINT,
@IN_MatSubTypeCode SMALLINT,
@UPDATE_Ver INT
DECLARE row_cursor INSENSITIVE CURSOR FOR
SELECT
MatNum,
VerNumMat,
MatDesc,
SpecSta,
OldMatID,
MatTypeCode,
MatSubTypeCode
FROM
DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec
WHERE
MatTypeCode IN (7,8,15)
OPEN row_cursor
FETCH NEXT FROM row_cursor
INTO @IN_MatNum, @IN_VerNumMat, @IN_MatDesc, @IN_SpecSta,
@IN_OldMatID, @IN_MatTypeCode, @IN_MatSubTypeCode
WHILE @@FETCH_STATUS = 0
BEGIN
IF(( SELECT COUNT(*)
FROM tb_CARDs_MTLInformation
WHERE MatNum = @IN_MatNum) = 0)
BEGIN
INSERT INTO tb_CARDs_MTLInformation
VALUES (@IN_MatNum, @IN_VerNumMat, @IN_MatDesc, @IN_SpecSta,
@IN_OldMatID, @IN_MatTypeCode, @IN_MatSubTypeCode)
END
ELSE
BEGIN
SELECT @UPDATE_Ver = NULL
SELECT
@IN_MatNum = MatNum,
@UPDATE_Ver = VerNumMat,
@IN_MatDesc = MatDesc,
@IN_SpecSta = SpecSta,
@IN_OldMatID = OldMatID,
@IN_MatTypeCode = MatTypeCode,
@IN_MatSubTypeCode = MatSubTypeCode
FROM
DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec
WHERE
MatNum = @IN_MatNum
AND SpecSta IN ('A', 'D')
GROUP BY
MatNum,
VerNumMat,
MatDesc,
SpecSta,
OldMatID,
MatTypeCode,
MatSubTypeCode
HAVING
VerNumMat = MAX(VerNumMat)
--######################################################################################################
--------------------------------------------------------------------------------------------------------
IF (@UPDATE_Ver IS NULL)
BEGIN
SELECT
@IN_MatNum = MatNum,
@UPDATE_Ver = VerNumMat,
@IN_MatDesc = MatDesc,
@IN_SpecSta = SpecSta,
@IN_OldMatID = OldMatID,
@IN_MatTypeCode = MatTypeCode,
@IN_MatSubTypeCode = MatSubTypeCode
FROM
DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec
WHERE
MatNum = @IN_MatNum
AND SpecSta IN ('H')
GROUP BY
MatNum,
VerNumMat,
MatDesc,
SpecSta,
OldMatID,
MatTypeCode,
MatSubTypeCode
HAVING
VerNumMat = MAX(VerNumMat)
--######################################################################################################
--------------------------------------------------------------------------------------------------------
IF (@UPDATE_Ver IS NULL)
BEGIN
SELECT
@IN_MatNum = MatNum,
@UPDATE_Ver = VerNumMat,
@IN_MatDesc = MatDesc,
@IN_SpecSta = SpecSta,
@IN_OldMatID = OldMatID,
@IN_MatTypeCode = MatTypeCode,
@IN_MatSubTypeCode = MatSubTypeCode
FROM
DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec
WHERE
MatNum = @IN_MatNum
AND SpecSta IN ('I')
GROUP BY
MatNum,
VerNumMat,
MatDesc,
SpecSta,
OldMatID,
MatTypeCode,
MatSubTypeCode
HAVING
VerNumMat = MAX(VerNumMat)
--######################################################################################################
--------------------------------------------------------------------------------------------------------
END
END
IF (@UPDATE_Ver IS not NULL)
UPDATE
tb_CARDs_MTLInformation
SET
VerNumMat = @UPDATE_Ver,
MatDesc = @IN_MatDesc,
SpecSta = @IN_SpecSta,
OldMatID = @IN_OldMatID,
MatTypeCode = @IN_MatTypeCode,
MatSubTypeCode = @IN_MatSubTypeCode
WHERE
MatNum = @IN_MatNum
END
FETCH NEXT FROM row_cursor
INTO @IN_MatNum, @IN_VerNumMat, @IN_MatDesc, @IN_SpecSta,
@IN_OldMatID, @IN_MatTypeCode, @IN_MatSubTypeCode
END
CLOSE row_cursor
DEALLOCATE row_cursor
END
END
I wish I had more time at the moment to experiment on this...I will work on devising something from the examples posted to attempt to get this working without a cursor...not only for this process but to institute into my knowledge base !!!
The place I work at right now seems to use cursors in everything...
I am new to the position of DBA...more of a learning role...my previous experience was mainly with report writing and process automation...nothing as indepth as what I am in the middle of at the moment so I am constantly playing catch up with the learning curve !!!
Thanks again guys for the comments and help...it is really appreciated when you take the time to help me.
Leeland
-
Lee,
Just curious... Everyone keeps telling you that cursors are not the way to go... why do you insist on using them?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am not insisting on using them like they are my favorite thing in the world...I inherited the code from the previous person who wrote something similar and I would love to sit down and learn a way around it as I have read a lot of articles regarding cursors and how they are not the best thing to use.
However at this time in my job (2 months) I am getting a TON of stuff tossed at me and I am just trying to keep my head above water, I know it works and that is the big thing right now for me as I have several other pressing issues to deal with and am not doing so hot at fixing them
.I mean no disrespect when I ask for help and then decide to use what I have, it is just me feeling comfortable that it works at this time and hopefully down the road I can revisit the topic and learn more from it.
Lee
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply