cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

cursor

Author

Topic

mana
Posting Yak Master

102 Posts

Posted - 2014-08-25 : 03:21:11

Hello ,

I have the following code and i wrote it with cursor but it takes too long to executte. Can you guide me how i can write it without cursor please?
thank you for your help

ALTER PROCEDURE [dbo].[usp_AMPPU_Teamleader]
AS
BEGIN
SET NOCOUNT ON;
--
DECLARE @Auftrag VARCHAR(50), @fehlercode VARCHAR(50), @Zeit_Fehler_Ein DATETIME,
@Teamleader_Von_Adresse VARCHAR(50), @Label_Von_Adresse VARCHAR(50), @Teamleader_Prüfplatz VARCHAR(50),
@Label_Prüfplatz VARCHAR(50), @Teamleader_Station VARCHAR(50), @Label_Station VARCHAR(50),
@Von_Adresse VARCHAR(50), @Prüfplatz VARCHAR(50), @Station VARCHAR(50),
@Teamleader VARCHAR(50), @Today DATE;
SET @Today = CASE WHEN LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 19) <= LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10) + ' 04:00:00' THEN LEFT(CONVERT(NVARCHAR, GETDATE()-1, 120), 10)
ELSE LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10) END;
--
DECLARE cr_Teamleader CURSOR FOR
SELECT Auftrag, Fehlercode, Zeit_Fehler_EIN, Von_Adresse, Prüfplatz, Station
FROM AMPPU_Alle_Fehlteile
WHERE Teamleader IS NULL
--AND LEFT(CONVERT(NVARCHAR, Zeit_Fehler_EIN, 120), 10) = @Today;
--
OPEN cr_Teamleader
FETCH NEXT FROM cr_Teamleader
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @Von_Adresse, @Prüfplatz, @Station
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
SELECT @Teamleader_Von_Adresse = Von_Adresse,
@Label_Von_Adresse = Label
FROM AMPPU_Teamleader_Von_Adresse
WHERE Von_Adresse = LEFT(@Von_Adresse,3)
OR Von_Adresse = @Von_Adresse;
--
SELECT @Teamleader_Prüfplatz = Prüfplatz,
@Label_Prüfplatz = Label
FROM AMPPU_Teamleader_Prüfplatz
WHERE Prüfplatz = @Prüfplatz;
--
SELECT @Teamleader_Station = Station,
@Label_Station = Label
FROM AMPPU_Teamleader_Station
WHERE Station = @Station;

--
SET @Teamleader = (SELECT CASE WHEN (LEFT(@Von_Adresse,3) = LEFT(@Teamleader_Von_Adresse,3) OR @Von_Adresse = @Teamleader_Von_Adresse) THEN @Label_Von_Adresse
WHEN @Prüfplatz = @Teamleader_Prüfplatz THEN
(SELECT CASE WHEN @Label_Prüfplatz IS NULL THEN @Label_Station ELSE @Label_Prüfplatz END)
END Teamleader);
--
--SELECT @Teamleader_Von_Adresse,
-- @Label_Von_Adresse,
-- @Teamleader_Prüfplatz,
-- @Label_Prüfplatz,
-- @Teamleader_Station,
-- @Label_Station,
-- @Teamleader as Teamleader,
-- @Auftrag,
-- @fehlercode,
-- @Zeit_Fehler_Ein
--

--SELECT Auftrag,Fehlercode,Zeit_Fehler_EIN,Teamleader from
--AMPPU_Alle_Fehlteile
--WHERE Auftrag = @Auftrag
--AND Fehlercode = @fehlercode
--AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);

--if @Teamleader IS NULL
-- SELECT @Teamleader,Auftrag,Fehlercode,Zeit_Fehler_EIN,Teamleader from
--AMPPU_Alle_Fehlteile
--WHERE Auftrag = @Auftrag
-- AND Fehlercode = @fehlercode
-- AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);

UPDATE AMPPU_Alle_Fehlteile
SET Teamleader = @Teamleader
WHERE CURRENT OF cr_Teamleader
-- WHERE Auftrag = @Auftrag
-- AND Fehlercode = @fehlercode
-- AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);
--
FETCH NEXT FROM cr_Teamleader
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @Von_Adresse, @Prüfplatz, @Station
--
END
--
CLOSE cr_Teamleader
DEALLOCATE cr_Teamleader
--
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-08-25 : 07:04:13

Duplicate of:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195886

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources