individual row loop is looping too much :/

hi guys,

im trying to loop through a table and call my function for each row. realise that this isn't the best use of sql functionality, but im still learning :D

the loop does work but calls the function, But, for each row it returns all the rows - before getting the next row id value :/

this is my code below

thanks

dan


declare @rowmin nchar(5)

select @rowmin = min(customerid)

from customers c

while exists

(select top 1 customerid

from customers c
where c.customerid > @rowmin)

begin

--proc here 

select dbo.moretotalorders(@rowmin) as 'total orders', @rowmin

from customers

--increment row var
 
select top 1 @rowmin = min(customerid) 

from customers c

where customerid > @rowmin

end

August 29th, 2015 7:20am

Typically cursor is used to process each row in a table.

Note though, that set based operation is the preferred way.

Cursor example: http://www.sqlusa.com/bestpractices2005/runningtotalusingcursor/

DECLARE CURSOR: https://msdn.microsoft.com/en-us/library/ms180169.aspx?f=255&MSPPError=-2147217396

Free Windows Admin Tool Kit Click here and download it now
August 29th, 2015 8:00am

thanks for the quick reply Kalman

I shall check out both of those solutions

cheers

dan

August 29th, 2015 8:10am

awesome! worked first time :D

that was using cursor - will also checkout the better way to do it

thanks

Free Windows Admin Tool Kit Click here and download it now
August 29th, 2015 8:27am

>> I am trying to loop through a table and call my function for each row. I realize that this isn't the best use of SQL functionality, but I am still learning. <<

The problem is that you come from procedural languages, and SQL is a declarative language. We do not have loops! Have you ever studied a totally different foreign language? English has no gender, but inflectional language do. Some of them gender verbs! Japanese verb tenses are nothing like Germanic or Slavic models. Welsh pronouns are weird. In Chinese, the future is just as real as the present; in English we predict (I will go to the store versus I am at the store and it is the future/past/present). 

Before you can drink new tea, you must first throw the old tea from your cup. Soto Zen proverb

>> the loop does work but calls the function, But, for each row it returns all the rows before getting the next row id value <<

There is no row_id in RDBMS. We locate a row by a key, which is a subset of its attributes. That "row_id" was record numbers in a file system which has records and not rows. They are as fundamentally different as the subject in an English sentence is from the topic in a Japanese sentence. 

Without DDL and sample data, what you posted is garbage. We have no way to guess what dbo.more_total_orders(@rowmin) means or does. In ISO-11179 and Metadata L8 rules a function or procedure is named <verb>_<object> no adjectives or adverbs, please. 

Want to post DDL and code and try again? 

August 29th, 2015 4:40pm

Hi danx551,

A CURSOR seems not to be necessary in this case. The dbo.moretotalorders is a scalar funcation, using below statement would lead to the same result as CURSOR does.

insert into zzzcustsumloop
select dbo.moretotalorders(customerid) as 'totalorders', customerid from customers


In my opinion,using CURSORs should be the last resort if there's no set-based fashion. See when is a CURSOR good.

If you have any question, feel free to let me know.

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 11:09pm

hi Eric,

interesting ... thanks - will try that

the cursor idea did work well actually - but I realise that im trying to apply my procedural coding ideas to sql - which is not the best :)

cheers

August 31st, 2015 5:24pm

:D you nailed I Eric - very cool ...and much less code :D

...so is sql effectively calling my function for each line returned anyway?

thanks

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 5:36pm

Yes, just try

select *, dbo.MoreTotalOrders(CustomerId) as TotalOrders from dbo.Customers

------------------

Also, what does this function MoreTotalOrders do? Can you post its definition? Scalar functions are also often best to be avoided and perhaps the logic of that function can be included directly into the query.

August 31st, 2015 5:45pm

hi Naomi - thanks

function below - I know it is not much - but just learning various things currently :)

cheers

dan

create function moretotalorders
(
 @p_customerid nvarchar(10)
)returns int
begin
 return (select count(orderid) from orders
 where customerid = @p_customerid)
end

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 5:59pm

It is always a good idea to give a meaningful name to the function. In this particular case it's a simple count of orders associated with a customer.

So, no need for such function, you can do

select C.*, (SELECT count(OrderId) as OrdersCount from Orders O where O.CustomerId = C.CustomerId) as OrdersCount

from dbo.Customers C

August 31st, 2015 6:05pm

yes good point

thanks for the info

yes it was just a practice at writing my first function

cheers

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 6:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics