Need help on Stored Procedure !!

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

General SQL Server Forums

New to SQL Server Programming

Need help on Stored Procedure !!

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-06 : 14:19:56

Hi,

following code is supposed to update Tabelle Immo with the data from the table tbl_sc_partner, where the immo.fordnr=tbl_sc_partner.fordnr Update all rows in Tab. Immo where the flag on Immo.mig_status=null.

I have to write a sp but it does not function! Who can help me there further !

many ... thanks

CREATE PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
-- Definition von Variablen und Konstanten
declare @NACHNAME nvarchar(40),
@VORNAME nvarchar(40),
@ENGAGEMENTNR nvarchar(40),
@FORDERGNR int,
@GLAEUBIGERNR int,
@KATEGORIE nvarchar(50),
@BELEGART nvarchar(50),
@MIG_ID int,
@MIG_STATUS varchar(20),
@FORDNR nvarchar(40)

if @mig_id=null
begin

declare sp_immo_meta_update_cur_1 cursor for
SELECT FORDNR, FORDERGNR, GLAEUBIGERNR, PARTNERID, NAME2, NAME1,GEBURTSDATUM, BEZSPARTE,ENGAGEMENT
FROM AdventureWorks.dbo.TBL_SC_PARTNER
WHERE Fordnr = @Fordnr
FOR UPDATE

open sp_immo_meta_update_cur_1
fetch next from sp_immo_meta_update_cur_1 into
@NACHNAME,
@VORNAME,
@ENGAGEMENTNR,
@FORDERGNR,
@GLAEUBIGERNR
while @@fetch_status = 0
begin

UPDATE immo
SET
NACHNAME=@NACHNAME,
VORNAME=@VORNAME,
ENGAGEMENTNR=@ENGAGEMENTNR,
FORDERGNR=@FORDERGNR,
GLAEUBIGERNR=@GLAEUBIGERNR
WHERE CURRENT OF sp_immo_meta_update_cur_1
fetch next from sp_immo_meta_update_cur_1 into @NACHNAME,
@VORNAME,
@ENGAGEMENTNR,
@FORDERGNR,
@GLAEUBIGERNR
end
close sp_immo_meta_update_cur_1
deallocate sp_immo_meta_update_cur_1
end
GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 14:45:40

Do you know your FETCH INTO Does not match the columns in your CURSOR?

ANOTHER SQL Server "Feature"

I would have expected that to fail

Oil Well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 14:50:55

All you need is this...but how do the tables relate?



UPDATE    I
	   SET	  NACHNAME		= P.FORDNR
			, VORNAME		= P.FORDERGNR
			, ENGAGEMENTNR	= P.GLAEUBIGERNR
			, FORDERGNR		= P.PARTNERID
			, GLAEUBIGERNR	= P.NAME2
	  FROM	  immo I
INNER JOIN    AdventureWorks.dbo.TBL_SC_PARTNER P
	 WHERE    i.???			= P.Fordnr

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-07 : 04:21:29

Still a logical error !! SP do not update table Immo !

ALTER PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
DECLARE @last_name VARCHAR(35),
@NACHNAME nvarchar(40),
@VORNAME nvarchar(40),
@ENGAGEMENTNR nvarchar(40),
@FORDERGNR int,
@GLAEUBIGERNR int,
@Fordnr nvarchar(40)
DECLARE mycursor CURSOR FOR
SELECT NAME1, NAME2, ENGAGEMENT, FORDERGNR, GLAEUBIGERNR
FROM dbo.TBL_SC_PARTNER
WHERE Fordnr in (Select fordnr from IMMO where MIG_ID is null)
OPEN mycursor
FETCH NEXT FROM mycursor INTO
@NACHNAME,
@VORNAME,
@ENGAGEMENTNR,
@FORDERGNR,
@GLAEUBIGERNR
WHILE @@fetch_status = 0
BEGIN
UPDATE I
SET NACHNAME = P.Name1
,VORNAME = P.Name2
,ENGAGEMENTNR = P.ENGAGEMENT
,FORDERGNR = P.FORDERGNR
,GLAEUBIGERNR = P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P
on i.Fordnr=P.Fordnr
WHERE CURRENT OF mycursor
FETCH NEXT FROM mycursor INTO
@NACHNAME,
@VORNAME,
@ENGAGEMENTNR,
@FORDERGNR,
@GLAEUBIGERNR
END
CLOSE mycursor
DEALLOCATE mycursor
GO

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-07 : 04:32:58

'@Return Value is -6 and nothing happens on table immo !!

Lookup Error - SQL Server Database Error: The cursor does not include the table being modified or the table is not updatable through the cursor.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-07 : 04:53:41

Brett wanted to tell you that there is no need to use a cursor for this.
But we can't help if we don't know the relation between the tables.
Best is to give:
table structure
some sample data
wanted result in relation to the sample data

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-07 : 07:47:11

quote:
Originally posted by webfred

Brett wanted to tell you that there is no need to use a cursor for this.
But we can't help if we don't know the relation between the tables.
Best is to give:
table structure
some sample data
wanted result in relation to the sample data

No, you're never too old to Yak'n'Roll if you're too young to die.

Ther are now Index or Constrainst between the Tabels but you can match immo.fordnr=tbl_sc_partner.fordnr the Fordnr is uniqui !
you can also match with immo.partnerid but its n:m Relation.

/****** Object: Table [dbo].[IMMO] Script Date: 07.03.2012 13:00:49 ******/
USE [dwdata];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[IMMO] (
[DWDOCID] int NOT NULL,
[DWPAGECOUNT] int NULL,
[DWDISKNO] int NULL,
[DWFLAGS] int NULL,
[DWOFFSET] int NULL,
[DWSTOREDATETIME] datetime NULL,
[DWSTOREUSER] nvarchar(20) NULL,
[DWMODDATETIME] datetime NULL,
[DWMODUSER] nvarchar(20) NULL,
[DWLASTACCESSDATETIME] datetime NULL,
[DWLASTACCESSUSER] nvarchar(20) NULL,
[DWSTORAGEACCESS] ntext NULL,
[ENGAGEMENTNR] nvarchar(40) NULL,
[NACHNAME] nvarchar(40) NULL,
[VORNAME] nvarchar(40) NULL,
[GEBURTSDATUM] datetime NULL,
[SPARTE] nvarchar(40) NULL,
[BEHLTER] nvarchar(20) NULL,
[STATUS] nvarchar(20) NULL,
[MATCHCODE] nvarchar(20) NULL,
[PRODUNIT] int NULL,
[PARTNERID] int NULL,
[DOKDATUM] datetime NULL,
[VBDATUM] datetime NULL,
[VBAKTZ] nvarchar(20) NULL,
[TITEL] int NULL,
[FORDNR] nvarchar(20) NULL,
[BARCODENR] nvarchar(38) NULL,
[MIG_ID] int NULL,
[MIG_STATUS] nvarchar(38) NULL,
[MIG_STATUS_DETAIL] nvarchar(255) NULL,
[KATEGORIE] nvarchar(40) NULL,
[BELEGART] nvarchar(40) NULL,
[PHIN_STATUS] int NULL,
[STAPELNUMMER] nvarchar(40) NULL,
[FORDERGNR] int NULL,
[GLAEUBIGERNR] int NULL,
[BRIEFID] nvarchar(20) NULL,
[BRIEFNR] nvarchar(20) NULL,
[FELD_29] nvarchar(40) NULL)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);

/****** Object: Table [dbo].[TBL_SC_PARTNER] Script Date: 07.03.2012 13:01:13 ******/
USE [dwdata];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[TBL_SC_PARTNER] (
[PRODUNIT] nvarchar(384) NULL,
[FORDNR] nvarchar(50) NULL,
[FORDERGNR] int NULL,
[GLAEUBIGERNR] int NULL,
[PARTNERID] nvarchar(384) NOT NULL,
[NAME2] varchar(35) NULL,
[NAME3] varchar(35) NULL,
[NAME1] varchar(35) NOT NULL,
[STRASSE] varchar(35) NULL,
[PLZ] varchar(6) NULL,
[ORT] varchar(28) NULL,
[GEBURTSDATUM] datetime NULL,
[BEZSPARTE] varchar(40) NULL,
[AKTENEIGNER] nvarchar(384) NOT NULL,
[ENGAGEMENT] varchar(25) NULL,
[PARTNERIDALT] nvarchar(384) NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO

INSERT INTO dbo.IMMO
(DWDOCID, DWPAGECOUNT, DWDISKNO, DWFLAGS, DWOFFSET, DWSTOREDATETIME, DWSTOREUSER, DWMODDATETIME, DWMODUSER, DWLASTACCESSDATETIME, DWLASTACCESSUSER, DWSTORAGEACCESS, ENGAGEMENTNR, NACHNAME, VORNAME, GEBURTSDATUM, SPARTE, BEHLTER, STATUS, MATCHCODE, PRODUNIT, PARTNERID, DOKDATUM, VBDATUM, VBAKTZ, TITEL, FORDNR, BARCODENR, MIG_ID, MIG_STATUS, MIG_STATUS_DETAIL, KATEGORIE, BELEGART, PHIN_STATUS, STAPELNUMMER, FORDERGNR, GLAEUBIGERNR, BRIEFID, BRIEFNR, FELD_29)
VALUES (86,3,1,2,0, '06.03.2012 10:46:19', 'ADMIN', '06.03.2012 10:46:19', 'ADMIN', ' ', null, ' ', ' ', 'Test', 'VORNAME', ' ', ' ', ' ', ' ', '10000030', 210, 280, '', '', '', , '3910100782', 'BARCODENR', MIG_ID, 'MIG_STATUS', 'MIG_STATUS_DETAIL', 'KATEGORIE', 'BELEGART', PHIN_STATUS, 'STAPELNUMMER', ' ', ' ', '10000', 'BRIEFNR', 'FELD_29')

INSERT INTO dbo.TBL_SC_PARTNER
(PRODUNIT, FORDNR, FORDERGNR, GLAEUBIGERNR, PARTNERID, NAME2, NAME3, NAME1, STRASSE, PLZ, ORT, GEBURTSDATUM, BEZSPARTE, AKTENEIGNER, ENGAGEMENT, PARTNERIDALT)
VALUES ('210', '3910100782', 0, 1, '280', 'Hans', 'NAME3', 'Schmitt', 'STRASSE', 'PLZ', 'ORT', 'GEBURTSDATUM', 'BEZSPARTE', 'Ford Bank', '1850953652', '0')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-07 : 08:25:08

[code]
select * from immo

UPDATE I
SET NACHNAME = P.NAME1
, VORNAME = P.NAME2
, ENGAGEMENTNR = P.ENGAGEMENT
, FORDERGNR = P.FORDERGNR
, GLAEUBIGERNR = P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr

select * from immo
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 08:52:48

Thank you Fred

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-07 : 10:57:16

quote:

Originally posted by webfred


select * from immo

UPDATE    I
	   SET	  NACHNAME	= P.NAME1
		, VORNAME	= P.NAME2
		, ENGAGEMENTNR	= P.ENGAGEMENT
		, FORDERGNR	= P.FORDERGNR
		, GLAEUBIGERNR	= P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr

select * from immo

No, you're never too old to Yak'n'Roll if you're too young to die.

THX for your replay but i get this error Msg:
Lookup Error - SQL Server Database Error: The cursor does not include the table being modified or the table is not updatable through the cursor.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 11:13:56

ummmm..this is your sproc




ALTER PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
UPDATE    I
	   SET	  NACHNAME	= P.NAME1
		, VORNAME	= P.NAME2
		, ENGAGEMENTNR	= P.ENGAGEMENT
		, FORDERGNR	= P.FORDERGNR
		, GLAEUBIGERNR	= P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr
GO

and I prefer to a drop and create instead of an alter

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-07 : 11:45:42

still same error Msg !!

Statement:07.03.2012 17:44:23 0:00:00.074: Executed Successfully
1: -- Execute Stored Proc dwdata.dbo.sp_immo_meta_update_cur_1

USE [dwdata];
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: sp_immo_meta_update_cur_1:42: TXT ja jo
Error:07.03.2012 17:44:24 0:00:00.003: Lookup Error - SQL Server Database Error: The cursor does not include the table being modified or the table is not updatable through the cursor.
6: DECLARE @return_value int;

EXEC @return_value = dwdata.dbo.sp_immo_meta_update_cur_1;

SELECT @return_value as N'@Return Value';
Script Execution:07.03.2012 17:44:29: Execution finished in 0:00:05.701

quote:
Originally posted by X002548

ummmm..this is your sproc

ALTER PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
UPDATE I
SET NACHNAME = P.NAME1
, VORNAME = P.NAME2
, ENGAGEMENTNR = P.ENGAGEMENT
, FORDERGNR = P.FORDERGNR
, GLAEUBIGERNR = P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr
GO

and I prefer to a drop and create instead of an alter

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-07 : 11:47:34

quote:
Originally posted by zero1de

still same error Msg !!

Statement:07.03.2012 17:44:23 0:00:00.074: Executed Successfully
1: -- Execute Stored Proc dwdata.dbo.sp_immo_meta_update_cur_1

USE [dwdata];
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: 3621:sp_immo_meta_update_cur_1:25: The statement has been terminated.
Connection:07.03.2012 17:44:29: sp_immo_meta_update_cur_1:42: TXT ja jo
Error:07.03.2012 17:44:24 0:00:00.003: Lookup Error - SQL Server Database Error: The cursor does not include the table being modified or the table is not updatable through the cursor.
6: DECLARE @return_value int;

EXEC @return_value = dwdata.dbo.sp_immo_meta_update_cur_1;

SELECT @return_value as N'@Return Value';
Script Execution:07.03.2012 17:44:29: Execution finished in 0:00:05.701

AND THIS is my SP:

CREATE PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
DECLARE @last_name VARCHAR(35),
@NACHNAME nvarchar(40),
@VORNAME nvarchar(40),
@ENGAGEMENTNR nvarchar(40),
@FORDERGNR int,
@GLAEUBIGERNR int,
@Fordnr nvarchar(40)
DECLARE mycursor CURSOR FOR
SELECT NAME1, NAME2, ENGAGEMENT, FORDERGNR, GLAEUBIGERNR
FROM dbo.TBL_SC_PARTNER
WHERE Fordnr in (Select fordnr from IMMO where MIG_ID is null)
OPEN mycursor
FETCH NEXT FROM mycursor INTO
@NACHNAME,
@VORNAME,
@ENGAGEMENTNR,
@FORDERGNR,
@GLAEUBIGERNR
WHILE @@fetch_status = 0
BEGIN

UPDATE I
SET NACHNAME = P.Name1
,VORNAME = P.Name2
,ENGAGEMENTNR = P.ENGAGEMENT
,FORDERGNR = P.FORDERGNR
,GLAEUBIGERNR = P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr
WHERE CURRENT OF mycursor
FETCH NEXT FROM mycursor INTO
@NACHNAME,
@VORNAME,
@ENGAGEMENTNR,
@FORDERGNR,
@GLAEUBIGERNR
END
CLOSE mycursor
Print'TXT ja jo'
DEALLOCATE mycursor
GO

quote:
Originally posted by X002548

ummmm..this is your sproc

ALTER PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
UPDATE I
SET NACHNAME = P.NAME1
, VORNAME = P.NAME2
, ENGAGEMENTNR = P.ENGAGEMENT
, FORDERGNR = P.FORDERGNR
, GLAEUBIGERNR = P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr
GO

and I prefer to a drop and create instead of an alter

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 13:20:11

?????

What's up?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-08 : 03:00:44

Hi,

i get still the same msg:
Error:07.03.2012 17:44:24 0:00:00.003: Lookup Error - SQL Server Database Error: The cursor does not include the table being modified or the table is not updatable through the cursor.
6: DECLARE @return_value int;

quote:
Originally posted by X002548

?????

What's up?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-08 : 08:34:08

I think you might misunderstand. Brett is saying your ENTIRE stored proc should look like this:

ALTER PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
UPDATE    I
	   SET	  NACHNAME	= P.NAME1
		, VORNAME	= P.NAME2
		, ENGAGEMENTNR	= P.ENGAGEMENT
		, FORDERGNR	= P.FORDERGNR
		, GLAEUBIGERNR	= P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr
GO

There should be NO cursor anywhere..

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-08 : 13:50:47

may well be that it could function but at a table with twenty million records would take too long! And I honestly would to solve the problem through a cursor. What is wrong with my cursor is not the? help me with the cursor guys !!

quote:
Originally posted by DonAtWork

I think you might misunderstand. Brett is saying your ENTIRE stored proc should look like this:
ALTER PROCEDURE [dbo].[sp_immo_meta_update_cur_1]
WITH EXEC AS CALLER
AS
UPDATE I
SET NACHNAME = P.NAME1
, VORNAME = P.NAME2
, ENGAGEMENTNR = P.ENGAGEMENT
, FORDERGNR = P.FORDERGNR
, GLAEUBIGERNR = P.GLAEUBIGERNR
FROM immo I
INNER JOIN TBL_SC_PARTNER P ON i.Fordnr = P.Fordnr
GO

There should be NO cursor anywhere..

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-09 : 03:13:43

The given solution would take a time for 20,000,000 rows that's right.
But a cursor is the very slowest solution ever and would take DAYS to get ready!

No, you're never too old to Yak'n'Roll if you're too young to die.

zero1de
Posting Yak Master

105 Posts

Posted - 2012-03-10 : 06:49:24

o.k understand webfred many thx for your help.

I just create a job for that SP, which runs every 60 seconds to keep the data in Tabel Immo Data up to date!!

quote:
Originally posted by webfred

The given solution would take a time for 20,000,000 rows that's right.
But a cursor is the very slowest solution ever and would take DAYS to get ready!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources