SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 cursor
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mana
Yak Posting Veteran

86 Posts

Posted - 08/25/2014 :  03:21:11  Show Profile
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

USA
15676 Posts

Posted - 08/25/2014 :  07:04:13  Show Profile  Visit robvolk's Homepage
Duplicate of:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195886
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000