Calling a SP inside a cursor loop..

I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. The pseudo code is as follows..

Create proc1 as
Begin

Variable declrations...

declare EffectiveDate_Cursor cursor for
select field1,fld2 from tab1,tab2 where tab1.effectivedate<Getdate()
--/////Assuming the above query would result in 3 records
Open EffectiveDate_Cursor
Fetch next From EffectiveDate_Cursor Into @.FLD1,@.FLD2
begin
/*Calling my second stored proc with fld1 as a In parameter
and Op1 and OP2 Out parameters*/
Exec sp_minCheck @.fld1, @.OP1 output,@.OP2 output
Do something based on Op1 and Op2.
end
While @.@.Fetch_Status = 0
Fetch next From EffectiveDate_Cursor Into @.FLD1,@.FLD2
/* Assume If loop count is 3.
and If the Fetch stmt is below the begin Stmt, the loop iterations are
4 else the loop iterations are 2*/
begin
/*Calling my second stored proc with fld1 as a In parameter and Op1
and OP2 Out parameters*/
Exec sp_minCheck @.fld1, @.OP1 output,@.OP2 output
Do something based on Op1 and Op2.
end

The problem I had been facing is that, the when a stored proc is called
within the loop, the proc is getting into infinite loops.
Any Help would be appreciated.

Satish(satishchandra999@.gmail.com) writes:
> I have SP, which has a cursor iterations. Need to call another SP for
> every loop iteration of the cursor. The pseudo code is as follows..
> Create proc1 as
> Begin
> Variable declrations...
>...
> While @.@.Fetch_Status = 0
> Fetch next From EffectiveDate_Cursor Into @.FLD1,@.FLD2
> /* Assume If loop count is 3.
> and If the Fetch stmt is below the begin Stmt, the loop iterations are
> 4 else the loop iterations are 2*/
> begin
> /*Calling my second stored proc with fld1 as a In parameter and Op1
> and OP2 Out parameters*/
> Exec sp_minCheck @.fld1, @.OP1 output,@.OP2 output
> Do something based on Op1 and Op2.
> end
>
> The problem I had been facing is that, the when a stored proc is called
> within the loop, the proc is getting into infinite loops.

May I guess: the inner process also uses cursors?

Anyway, the proper way to program a cursor loop is:

DECLARE cur INENSITIVE CURSOR FOR
SELECT ...
-- Error handling goes here

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @.x, @.y, ...
IF @.@.fetch_status <> 0
BREAK

-- Do stuff
END

DEALLOCATE cur

By using only one FETCH statements you avoid funny errors, when you change
the cursor and forgets to change the cursor at the end of the loop. And by
checl @.@.fetch_status directly after the FETCH, you know that @.@.fetch_status
relates to that FETCH.

... and in case no one ever told you before: avoid iterations as much as
you can, and try to always work set-based. Yes, I can understand that you
want to reuse code, and if the oomplexity is high enough it may be
warranted if the number of rows in the cursor is moderate. But the cost
in performance for iterative solutions can be *enourmous*. A database
engine is simply not designed for this type of processing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. <<

No. You need to learn to program in SQL. All you are doing is
mimicing a 1960's 3GL magnetic tape file system . In your pseudo
code, you even refer to fields instead of columns! You put the "sp_"
prefix on procedure names!

Don't you understand that SQL is a non-procedurdal language? You
should write only a few cursors in 20 years, not two in one
application.

Your whole approach to the problem is **fundamentally** wrong.

>> The problem I had been facing is that, the when a stored proc is called within the loop, the proc is getting into infinite loops. <<

It is very hard to de-bug code that you will not show us. But when
pseudo code is this awful, I bet that the real code is a total mess.
More cursors? Dynamic SQL? Badly written procedural code with poor
coupling and cohesion?

>> Any Help would be appreciated. <<

You have no idea what you are doing. What you will get on Newsgroups
is a quick kludge to get rid of you, but not any real help. You need
to stop programming and get some education; then get some training.

Call function from a procedure and assign return value to variable (sql server)

Hi all,
I have a procedure proc1, which needs to update a table.
One of the columns to update the table is calculated using a function
funct1 (which returns a smalldatetime)
My problem is how do I call the function funct1 from procedure proc1,
so that the return value of function is saved in a variable in
procedure.
Sample Procedure Proc1
ALTER PROCEDURE proc1
@.x int
AS
declare @.ret smalldatetime
--Here, I want @.ret to be a smalldatetime value returned by function
funct1.
--I tried the below (though knowing it wont work):
--@.ret = select dbo.funct1(param1), but it gives error.
--I even tried(again, though knowing it wont work):
--update myTable set field1 = select dbo.funct1(param1), but it gives
error.
--WHERE ...
update myTable set field1 = @.ret
WHERE ...
Sample function funct1:
alter FUNCTION dbo.funct1
(
@.val1 decimal,
@.val2 int
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @.ret1 decimal
DECLARE @.ret2 smalldatetime
DECLARE sel_Cursor CURSOR FOR SELECT field1, field2
FROM myTable
OPEN sel_Cursor
FETCH NEXT FROM sel_Cursor INTO @.ret1, @.ret2
CLOSE sel_Cursor
DEALLOCATE sel_Cursor
if @.val1 = @.ret2
return @.ret2
else
set @.ret2 = some function...
RETURN @.ret2
END
---
Pls help..
TIA..Whats the error ? Normally you could use it by using this sample query
SELECT @.ret = dbo.SomeFunction(@.SomeParam)
HTH, Jens Suessmeyer.|||Thanks guys..
Damn, I dont knw why i was using the 'select'. Now it worked for me.
Trey,
I am using the cursor to fetch value and assign it to a variable.
The 'where' clause fetches single row.
And the select query returns single value.
But still i have used cursor coz thts the only way I know (I am pretty
new to SQL :( ).
Is there any other way to do so'
Trey Walpole wrote:
> What is that cursor supposed to be doing?
> Are you really looping through the whole table to get a single value'
> And possibly some random value at that?
> i cannot in good conscience give you an answer and let you keep that UDF..
.
>
> Chris wrote:|||Chris wrote:
> Hi all,
> I have a procedure proc1, which needs to update a table.
> One of the columns to update the table is calculated using a function
> funct1 (which returns a smalldatetime)
> My problem is how do I call the function funct1 from procedure proc1,
> so that the return value of function is saved in a variable in
> procedure.
>
> Sample Procedure Proc1
> ALTER PROCEDURE proc1
> @.x int
> AS
> declare @.ret smalldatetime
> --Here, I want @.ret to be a smalldatetime value returned by function
> funct1.
> --I tried the below (though knowing it wont work):
> --@.ret = select dbo.funct1(param1), but it gives error.
> --I even tried(again, though knowing it wont work):
> --update myTable set field1 = select dbo.funct1(param1), but it gives
> error.
> --WHERE ...
> update myTable set field1 = @.ret
> WHERE ...
>
> Sample function funct1:
> alter FUNCTION dbo.funct1
> (
> @.val1 decimal,
> @.val2 int
> )
> RETURNS smalldatetime
> AS
> BEGIN
> DECLARE @.ret1 decimal
> DECLARE @.ret2 smalldatetime
> DECLARE sel_Cursor CURSOR FOR SELECT field1, field2
> FROM myTable
> OPEN sel_Cursor
> FETCH NEXT FROM sel_Cursor INTO @.ret1, @.ret2
> CLOSE sel_Cursor
> DEALLOCATE sel_Cursor
> if @.val1 = @.ret2
> return @.ret2
> else
> set @.ret2 = some function...
> RETURN @.ret2
> END
> ---
> Pls help..
> TIA..
Why does the function use a cursor? Why don't you just assign the
result of the function to a variable?
Please post DDL, sample data and required results as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Chris wrote:
> Thanks guys..
> Damn, I dont knw why i was using the 'select'. Now it worked for me.
> Trey,
> I am using the cursor to fetch value and assign it to a variable.
> The 'where' clause fetches single row.
> And the select query returns single value.
> But still i have used cursor coz thts the only way I know (I am pretty
> new to SQL :( ).
> Is there any other way to do so'
>
In that case you probably don't need the cursor or the function. You
can do it in the UPDATE. For example:
UPDATE your_table
SET col =
(SELECT col
FROM other_table
WHERE ...)
WHERE ...
etc.
Set-based code should be your first choice for this kind of thing. If
you are new to SQL then you shouldn't be using cursors AT ALL. You can
do a lot of damage that way.
David Portas
SQL Server MVP
--|||Hey david,
In the function, I am doing calculation with two different tables, and
the value returned is compared with the parameter i am passing in the
function.
If the values are same, I am not supposed to change the value in
tables.
If its different, some calculations are made, and the new value is
inserted.
For this, I am using cursor, because I need to save value to a variable
to compare, as well as calculate.|||Chris wrote:
> Hey david,
> In the function, I am doing calculation with two different tables, and
> the value returned is compared with the parameter i am passing in the
> function.
> If the values are same, I am not supposed to change the value in
> tables.
> If its different, some calculations are made, and the new value is
> inserted.
> For this, I am using cursor, because I need to save value to a variable
> to compare, as well as calculate.
A) You don't need to use a cursor to assign a value to a variable. You
can use SET
SET @.var =
(SELECT col
FROM your_table
WHERE ...)
For multiple assignment you can use SELECT:
SELECT @.col1 = col1, @.col2 = col2
FROM your_table
WHERE ...
B) You don't need a function to do all the joins and calculations you
talked about. You should be able to do that in an UPDATE statement in
almost every case. Most cases, doing it in the UPDATE statement will
perform better than using a function.
Unfortunately you didn't give us enough information to reply with
alternative solutions. That's why the convention is to post DDL, sample
data, and your required end results. I posted a link earlier that
explained how to do that.
Let me explain why I said you shouldn't use cursors. I didn't meant
that as serious advice rather than a rebuke. The reason is that most of
the time cursors are a bad choice for solving problems in SQL. Those
who don't know SQL well tend to write over-complex, inefficient and
buggy cursor code. Only when you know SQL really well will you have the
experience to know when a cursor is a sensible idea. Until then it
really is safest to avoid them altogether.
Hope this helps.
David Portas
SQL Server MVP
--|||Thanx for the help Dave,
I will try doing it this way..