Cursor vs. temporary table. Declaring with local variables 1

[uerobertson]

I am writing a stored procedure that will take in a table name and some field names and use the info from that table to update or add records to another table. I can't just use the

UPDATE INTO

because an ID is retrieved from yet another table. (Not my table design - came down from higher up). My questions are these:

Can I declare a

CURSOR

for a

SELECT

statement that uses local variables for table and field names?
i.e.

DECLARE CURSOR FOR
SELECT @field1, @field2 FROM @table1

Is a

CURSOR

the best way to go about this?

Looking through the threads it seems that most of the time the recommendation is to use a temporary table or a view. Is there a general recommendation that can be made of

CURSOR

vs.

#TABLE

vs.

VIEW

?

I am using a

CURSOR

because I need to go through each record individually as I am calling another procedure to do the actual updates. I am trying to keep each procedure as general as possible as each could be used for several different tables. I don't want to end up with 15 procedures that all do the same thing. Extensibility is more important than speed.

Thank you for all your help.

Ursula

 

[royjimenez]

i have used #temporal tables, if u use a cursor the server will get lock while the cursor exists.

Other way:

create table PRUEBA (
id int default dbo.getNumber(),
name char(50)
)


and u can make a function to get the new ID.

ALTER FUNCTION getNumber() RETURNS int
AS BEGIN
... // your logic to get new ID





Regards..

 

[JayKusch]

You may want to build your queries Dynamically based on the tables and fields that are sent to your SP. Drop a few more lines of code on us so that we may better understand what you are trying to accomplish.

Thanks

J. Kusch

 

[royjimenez]

i have used #temporal tables, if u use a cursor the server will get lock while the cursor exists.

Other way ( u can develop your store procedures without cursors if u can get a ID in the same way than an Identity Field. You can make a Function called in this example getNumber() to return your own ID, but u will get it without to have to call your SP to get your ID. It's almost the same, only u have to make a new function and in the table's structure you must add "id int default dbo.getNumber()".


create table PRUEBA (
id int default dbo.getNumber(),
name char(50)
)

NOW, u can make your new function to get your own ID.

-- i am assuming the serie always exists
ALTER FUNCTION getNumber() RETURNS int
AS BEGIN

declare @int

update MySeries
set CurrentValue = CurrentValue + 1
where Table = "MyTable" -- it should be a param...

select @s = MySeries
from MySeries
where Table = "MyTable" -- it should be a param...

return @s

...



Regards.





Regards..

 

[uerobertson]

Ok, here's my code. The

CURSOR

is currently hardcoded for the table name and field names. I would like to be able to pass them into the updateRNFdesc sp instead.

The Results table is created by the Import Data... Wizard from an Access table. This is why I would like to pass the table and field names in, next time the descriptions are updated the data may not be coming from Access and the fields may be named differently. The descriptions come from an outside source.

The sp addUpdateColourDescription will add a description or update an existing one depending on whether the record already exists. The function to get the colourID is in this sp.

CREATE PROCEDURE dbo.updateRNFdesc
AS

DECLARE @descTableName AS varchar(50) -- new descriptions table
DECLARE @descFieldName AS varchar(50) -- new descriptions field
DECLARE @colourFieldName AS varchar(50) -- Colour Code field 
DECLARE @locale AS char(2) -- locale of description
DECLARE @NRFColourCode AS varchar(50) -- NRF colour code
DECLARE @NRFDescription AS varchar(255) -- NRF colour description
DECLARE @errorCode AS int -- return code from procedures called

SET @descTableName = 'Results'
SET @descFieldName = 'Color'
SET @colourFieldName = 'Color Code'
SET @locale = 'en'

-- Update all NRF descriptions
-- Get colour descriptions and codes

DECLARE newColourDescriptions CURSOR 
	FOR SELECT Color, [COLOR=Code] FROM Results

OPEN newColourDescriptions

-- Get first record
FETCH NEXT FROM newColourDescriptions
INTO @NRFDescription,@NRFColourCode
-- While there are more records
WHILE @@FETCH_STATUS = 0
BEGIN
	-- do add/update
	EXECUTE @errorCode = addUpdateColourDescription @locale, @NRFColourCode, @NRFDescription
	
	-- process error code	
	-- null parameter
	IF @errorCode = 1
	BEGIN
		PRINT 'One or more parameters is NULL.'
		-- close cursor and deallocate memory
		CLOSE newColourDescriptions
		DEALLOCATE newColourDescriptions
		-- Quit because can't do any processing without all parameters		
		RETURN(1)
	END
	-- other error - insertion error etc.
	-- print error code and continue
	ELSE IF @errorCode <> 0
	BEGIN
		PRINT 'Error occured: Code ' + @errorCode
	END
	
	-- Get next row
	FETCH NEXT FROM newColourDescriptions
	INTO @NRFDescription,@NRFColourCode

END -- while there are more records
-- close cursor and deallocate memory
CLOSE newColourDescriptions
DEALLOCATE newColourDescriptions

GO

Thanks for your help.

Ursula

 

[royjimenez]

I think that u can resolve your problem with dynamic cursors


i am sending u an example that i have done to replicate information, try to get all that u need:


CREATE PROC ba_conc_saldos_diarios
@tableowner sysname, -- this is the publisher owner, but we assume its always dbo at subscriber.
@tablename sysname, -- this is the publisher tablename, but we assume its identical at subscriber.
@rowguid uniqueidentifier,
@subscriber sysname,
@subscriber_db sysname,
@log_conflict int OUTPUT, -- output param for if to log conflict for later resolution.
@conflict_message nvarchar(512) OUTPUT -- output param for message to be given about resolution if conflict is loggged.

AS

declare @sqlstr varchar(7000),
@pub_qualified_name varchar(392),
@sub_qualified_name varchar(392),
@empresa varchar(5),
@cuenta decimal(8),
@fcierre datetime,
@finicio datetime,
@ffinal datetime
--sfcierre varchar(20)

declare @mon_salini_sub decimal(15,2),
@mon_salini decimal(15,2),
@mon_salfin decimal(15,2),
@mon_salfin_sub decimal(15,2)

SELECT @pub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename)
SELECT @sub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename)

-- Obtener los datos de Publicador
select @sqlstr = "SELECT SALDO_INI, SALDO_ACT FROM " + @pub_qualified_name + " where ROWGUIDCOL = '" + convert(varchar(36),@rowguid) + "'"
select @sqlstr = "DECLARE lcRepl CURSOR GLOBAL FAST_FORWARD FOR " + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcRepl
fetch next from lcRepl into @mon_salini, @mon_salfin
close lcRepl
deallocate lcRepl

-- Recuperar los datos del Subscriptor
select @sqlstr = 'SELECT * FROM ' + @sub_qualified_name + &quot; where ROWGUIDCOL = '&quot; + convert(varchar(36),@rowguid) + &quot;'&quot;
select @sqlstr = &quot;select COD_EMPRESA, ID_CUENTA, FEC_CIERRE from openquery(&quot; + QUOTENAME(@subscriber) + ',&quot;' + @sqlstr + '&quot'
select @sqlstr = 'DECLARE lcReplSaldos CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcReplSaldos
fetch next from lcReplSaldos into @empresa, @cuenta, @fcierre
close lcReplSaldos
deallocate lcReplSaldos

select @finicio = convert(datetime, convert(char(4), datepart(year, @fcierre)) + &quot;-&quot; + convert(varchar(2), datepart(month, @fcierre)) + &quot;-&quot; + convert(varchar(2),datepart(day, @fcierre)))
select @ffinal = dateadd(day, 1, @finicio)

-- Obtener el Detalle de Movimientos
select @sqlstr = QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename + '_DESGLOSE')
select @sqlstr = &quot;select * from &quot; + @sqlstr + &quot; where COD_EMPRESA = '&quot; + @empresa + &quot;' and ID_CUENTA = &quot; + convert(varchar(10), @cuenta) + &quot; and FEC_CIERRE >= '&quot; + convert(varchar(30), @finicio) + &quot;' and FEC_CIERRE < '&quot; + convert(varchar(30), @ffinal) + &quot;'&quot;
select @sqlstr = 'select COD_EMPRESA, ID_CUENTA, FEC_CIERRE, sum(SALDO_INI), sum(SALDO_ACT) from openquery(' + QUOTENAME(@subscriber) + ',&quot;' + @sqlstr + '&quot group by COD_EMPRESA, ID_CUENTA, FEC_CIERRE'
select @sqlstr = &quot;DECLARE lcReplSaldosDet CURSOR GLOBAL FAST_FORWARD FOR &quot; + @sqlstr
execute ( @sqlstr )
if @@error <> 0 goto FALLO
open lcReplSaldosDet
fetch next from lcReplSaldosDet into @empresa, @cuenta, @fcierre, @mon_salini_sub, @mon_salfin_sub
close lcReplSaldosDet
deallocate lcReplSaldosDet

-- Calcular el Monto de Desfase
select @mon_salini = isnull(@mon_salini,0) + isnull(@mon_salini_sub,0)
select @mon_salfin = isnull(@mon_salfin,0) + isnull(@mon_salfin_sub,0)

select @sqlstr = QUOTENAME(@subscriber_db)+'.'+QUOTENAME(@tableowner)+'.'+QUOTENAME(@tablename + '_DESGLOSE')
select @sqlstr = &quot;delete from [&quot; + @subscriber + &quot;].&quot; + @sqlstr + &quot; where COD_EMPRESA = '&quot; + @empresa + &quot;' and ID_CUENTA = &quot; + convert(varchar(10), @cuenta ) + &quot; and FEC_CIERRE >= '&quot; + convert(varchar(30), @finicio) + &quot;' and FEC_CIERRE < '&quot; + convert(varchar(30), @ffinal) + &quot;'&quot;
execute( @sqlstr )
if @@error <> 0 goto FALLO

-- Devolver el Cursor
SELECT COD_EMPRESA, ID_CUENTA, FEC_CIERRE, @mon_salini, @mon_salfin, @rowguid
FROM BA.BA_SALDOS_DIARIOS
WHERE rowguid=@rowguid
if @@error <> 0 goto FALLO

RETURN 1

FALLO:
raiserror 40001 &quot;Error al realizar la replicacion&quot;
return -1
go

 

[Bygbobbo]

user defined function executable within a stored proc should do the trick
make your addUpdateColourDescription a user defined function

CREATE PROCEDURE dbo.updateRNFdesc
@locale AS char(2),
@NRFColourCode AS varchar(50),
@NRFDescription AS varchar(255)
@descTableName AS varchar(50)
AS

Exec('select
[database].dbo.addUpdateColourDescription ['+@locale+'],['+@NRFColourCode+'],['+ @NRFDescription+']
from '+@descTableName)
GO

Cursors should only be used as a last resort. When your at that point ask for advice cause I still wouldnt use a cursor.


--Bygs

 

[uerobertson]

Hi Bygbobbo,
I'm sorry but I don't understand your post. Why should I convert the stored procedure to a function? I don't understand how that would solve my problem with the cursor. I would still have to retrieve each record before calling the function. Everything in the updateRNFDesc sp works great except the dynamic cursor (or whatever we replace it with).

Would you be able to explain further? Please forgive me if I'm just being dim.

Thanks,
Ursula

 

[uerobertson]

Hi royjimenez,
Thanks for the cursor with variables stuff. It works great. From what I've read though cursors are evil so I'm still looking for an alternative.

Thanks very much,
Ursula

 

[Bygbobbo]

What I have posted requires no cursor....

It will run the updates as records are being pulled dynamically.....

I thought you were looking for a way to get the same thing done without cursors, which is what I recommend.

Cursors should only be used as a last resort..... Very last resort.

If you are happy with what you have now then never mind the complexities of what I have posted. Otherwise, let me know I will give you a detailed explanation of my post and reasons why cursors are very bad..

Bygs..

 

[uerobertson]

Hi Bygbobbo,
Yes, I am definitely looking for a way to do the updating without cursors. I think I understand now so let me see if I've got this right.

If I make the addUpdate a function and use it in a SELECT statement, the function will be run on every record generated by the SELECT.

I would appreciate it if you could also tell me why cursors are bad so I can know when and when not to use them in the future. (Mostly going to be 'when nots' I realize

Thanks,
Ursula

 

[royjimenez]

Ursula, cannot u make your own getID() function to get the Identities ?

'cause if u make it in the way, u can make your store procedures with no problems.

?
I wrote something before about it

Regards.

 

[Bygbobbo]

Yes, the function will be run on every record. A lot less code to manage also for that matter.

A couple of reasons not to use cursors:
(1) very resource intensive and slow.
(2) may cause some bad table locking(when multiple instances are run) which will render your Database tables useless.
(3) let's say your cursor fails how do you know where it left off, especially with an insert?


Bygs.

 

[uerobertson]

Thanks Bygs.

That answered my questions.

U.

 

[uerobertson]

Hi Guys,
I'm back. The addUpdate procedure above got put on the back burner for a few days but now I have begun to work on it again.

I have a problem. When I changed the procedure to a function it gave the error:

Only functions and extended stored procedures can be executed from within a function.

The line it's refering to is a dynamically generated (and executed, using sp_executesql) SELECT statement.
There are also UPDATE and INSERT statements in the addUpdate procedure. I have read that you cannot create a function with these kind of statements.

Is it still feasible to change my sp to a function?

Thanks,
Ursula