Cursors vs. Queries: closest

Showing posts with label closest. Show all posts

Sunday, March 25, 2012

Cursor for loops?

Is there something in T-SQL that is the same as FOR loops in Oracle PL/SQL?
if not, what is the closest equivalent?
DECLARE cursor_name CURSOR FOR
SELECT some_column1, some_column2 FROM some_tables
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @.some_column1, @.some_column2
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*
do something with data
*/
FETCH NEXT FROM cursor_name INTO @.some_column1, @.some_column2
END
CLOSE cursor_name
DEALLOCATE cursor_name
Read "SQL Server Books Online" about cursors.
Hilarion
sql

Cursor for loops?

Is there something in T-SQL that is the same as FOR loops in Oracle PL/SQL?
if not, what is the closest equivalent?DECLARE cursor_name CURSOR FOR
SELECT some_column1, some_column2 FROM some_tables
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @.some_column1, @.some_column2
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*
do something with data
*/
FETCH NEXT FROM cursor_name INTO @.some_column1, @.some_column2
END
CLOSE cursor_name
DEALLOCATE cursor_name
Read "SQL Server Books Online" about cursors.
Hilarion

Cursor for loops

Is there something in T-SQL that is the same as FOR loops in Oracle PL/SQL?
if not, what is the closest equivalent?i don't know Oracle however have a look at ...
WHILE @.i < 100
BEGIN
-- your task
SET @.i = @.i + 1
END
i hope it helps
ian
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:Oy%23fjvDsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Is there something in T-SQL that is the same as FOR loops in Oracle
> PL/SQL?
> if not, what is the closest equivalent?
>
>|||SQL Server has a WHILE loop... but cursors, loops and procedural processing
are best avoided. Try to write set-based code for all your data manipulation.
Post here if you need help.
--
David Portas
SQL Server MVP
--|||No, no, I didn't mean simply 'for loops', but 'cursor for loops'.
They look like this in Oracle:
FOR mycursor IN (SELECT PERSON_ID, NAME FROM T_PERSON)
LOOP
doSomething(mycursor.PERSON_ID);
END LOOP;
I think this is a very useful construct, therefore I'm looking for something
similar in T-SQL.
"Ian Oldbury" <ian_oldbury@._NO_msn_SPAM_.com> wrote in message
news:Orj1$wDsEHA.2136@.TK2MSFTNGP14.phx.gbl...
> i don't know Oracle however have a look at ...
> WHILE @.i < 100
> BEGIN
> -- your task
> SET @.i = @.i + 1
> END
>
> i hope it helps
> ian
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:Oy%23fjvDsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Is there something in T-SQL that is the same as FOR loops in Oracle
> > PL/SQL?
> > if not, what is the closest equivalent?
> >
> >
> >
>|||That sort of loop is normally only useful if the operation represented by
"doSomething" is something inherently procedural, like sending an email or
invoking some other external process. For those occassions you can use a
WHILE loop with a cursor (better still, do that process at the client or
middle-tier). See Books Online for examples of cursors.
For data manipulation you should avoid using cursors and loops.
--
David Portas
SQL Server MVP
--|||a cursor does have its uses however most things can be done by avioding
them, as they're a heafty resource.
DECLARE
cReOrder CURSOR LOCAL FOR
SELECT @.ID, @.DisplayOrderFROM
<table>
WHERE <whatever>
OPEN cReOrder
FETCH cReOrder INTO @.ID, @.DisplayOrder
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- INSERT OR WHATEVER
FETCH NEXT FROM cReOrder INTO @.ID, @.DisplayOrder
END
CLOSE cReOrder
DEALLOCATE cReOrder
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ejO$IHEsEHA.536@.TK2MSFTNGP11.phx.gbl...
> No, no, I didn't mean simply 'for loops', but 'cursor for loops'.
> They look like this in Oracle:
> FOR mycursor IN (SELECT PERSON_ID, NAME FROM T_PERSON)
> LOOP
> doSomething(mycursor.PERSON_ID);
> END LOOP;
> I think this is a very useful construct, therefore I'm looking for
> something
> similar in T-SQL.
>
> "Ian Oldbury" <ian_oldbury@._NO_msn_SPAM_.com> wrote in message
> news:Orj1$wDsEHA.2136@.TK2MSFTNGP14.phx.gbl...
>> i don't know Oracle however have a look at ...
>> WHILE @.i < 100
>> BEGIN
>> -- your task
>> SET @.i = @.i + 1
>> END
>>
>> i hope it helps
>> ian
>> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
>> news:Oy%23fjvDsEHA.516@.TK2MSFTNGP09.phx.gbl...
>> > Is there something in T-SQL that is the same as FOR loops in Oracle
>> > PL/SQL?
>> > if not, what is the closest equivalent?
>> >
>> >
>> >
>>
>|||There are customers in SQL Server. Check out DECLARE. (Not the DECLARE that you declare variables,
but the one with which you declare a cursor.) Then you combine that with a while loop. There are
examples in Books Online.
As already mentioned, most problems can be solved with set based logic (typically a single SELECT,
INSERT, UPDATE or DELETE) and that is most often much much faster than looping using a cursor.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message news:ejO$IHEsEHA.536@.TK2MSFTNGP11.phx.gbl...
> No, no, I didn't mean simply 'for loops', but 'cursor for loops'.
> They look like this in Oracle:
> FOR mycursor IN (SELECT PERSON_ID, NAME FROM T_PERSON)
> LOOP
> doSomething(mycursor.PERSON_ID);
> END LOOP;
> I think this is a very useful construct, therefore I'm looking for something
> similar in T-SQL.
>
> "Ian Oldbury" <ian_oldbury@._NO_msn_SPAM_.com> wrote in message
> news:Orj1$wDsEHA.2136@.TK2MSFTNGP14.phx.gbl...
>> i don't know Oracle however have a look at ...
>> WHILE @.i < 100
>> BEGIN
>> -- your task
>> SET @.i = @.i + 1
>> END
>>
>> i hope it helps
>> ian
>> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
>> news:Oy%23fjvDsEHA.516@.TK2MSFTNGP09.phx.gbl...
>> > Is there something in T-SQL that is the same as FOR loops in Oracle
>> > PL/SQL?
>> > if not, what is the closest equivalent?
>> >
>> >
>> >
>>
>|||Hi David,
I've been trying to find out that point at which cursor or blah blah are
actually acceptable to use.
At what point is it better to use the cursor instead of the other available
options. What tips have you got...?
cheers for your thoughts
ian
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> SQL Server has a WHILE loop... but cursors, loops and procedural
> processing
> are best avoided. Try to write set-based code for all your data
> manipulation.
> Post here if you need help.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi David,
Can you please let me know how to use the 'set based code' for substituting
the cursor usage?
Normally I use the cursor to go through a set of rows and do some actions
with the values from each row (like calling another procedure, passing a
column value from that row). How can I achieve the same with the 'set based
code'?
Arun
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> SQL Server has a WHILE loop... but cursors, loops and procedural
processing
> are best avoided. Try to write set-based code for all your data
manipulation.
> Post here if you need help.
> --
> David Portas
> SQL Server MVP
> --
>|||I'd estimate that for most people using SQL Server at least 99.99% of data
manipulation can be done without a cursor. Inevitably there are inherently
procedural tasks that are not regular data manipulation and these are what I
would call acceptable uses of a cursor. I'm talking about things like sending
emails and automated management processes such as monitoring databases,
updating statistics, rebuilding indexes, managing backups, etc.
Sometimes operational constraints (as distinct from technical ones) may
compel you to use a cursor where you might be better off without. For example
some existing code in a stored procedure that needs to be invoked for each
row of a set and where you haven't the remit or the resources to rewrite that
SP.
There is also a relatively small class of tasks which don't have feasible
set-based solutions but where a cursor proves more efficient. These are rare
in my experience although one or two examples have been discussed in this
group and elsewhere. If you think you have encountered one of these then
always get a second opinion. Post the problem here if you like.
Finally there is what anecdotal evidence suggests is by far the most common
use of a cursor: those written by developers familiar with procedural
languages who don't know SQL well enough to write effective set-based code.
There are lots of examples of these. Unfortunately this code is usually
inefficient, doesn't scale well and is often very costly to support and
maintain. As a guide, I haven't written a single cursor for an actual
data-manipulation task in the last 5 years. So if you are writing curors
(except for admin-type tasks) regularly then you should certainly think again
about whether your code is as efficient, reliable and maintainable as it
ought to be.
--
David Portas
SQL Server MVP
--|||> Normally I use the cursor to go through a set of rows and do some actions
> with the values from each row (like calling another procedure, passing a
> column value from that row). How can I achieve the same with the 'set based
> code'?
See the reply I posted in response to Ian. If you *must* invoke a stored
procedure for each row then yes, you have to use a cursor or equivalent
procedural loop. But if that SP just performs straight data manipulation then
you're probably better off rewriting it so that you can perform the operation
once for a whole data set rather than individually for each row. There isn't
a generic answer to your question because it depends on what the SP does.
--
David Portas
SQL Server MVP
--
"Arun" wrote:
> Hi David,
> Can you please let me know how to use the 'set based code' for substituting
> the cursor usage?
> Normally I use the cursor to go through a set of rows and do some actions
> with the values from each row (like calling another procedure, passing a
> column value from that row). How can I achieve the same with the 'set based
> code'?
> Arun
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > SQL Server has a WHILE loop... but cursors, loops and procedural
> processing
> > are best avoided. Try to write set-based code for all your data
> manipulation.
> > Post here if you need help.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>
>|||Hi David,
I shall decribe the situation to you. Then you can suggest me if some thing
else can be done for this..
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:3F18A963-CC4F-4412-9271-CA4756441EEB@.microsoft.com...
> > Normally I use the cursor to go through a set of rows and do some
actions
> > with the values from each row (like calling another procedure, passing a
> > column value from that row). How can I achieve the same with the 'set
based
> > code'?
> See the reply I posted in response to Ian. If you *must* invoke a stored
> procedure for each row then yes, you have to use a cursor or equivalent
> procedural loop. But if that SP just performs straight data manipulation
then
> you're probably better off rewriting it so that you can perform the
operation
> once for a whole data set rather than individually for each row. There
isn't
> a generic answer to your question because it depends on what the SP does.
> --
> David Portas
> SQL Server MVP
> --
>
> "Arun" wrote:
> > Hi David,
> >
> > Can you please let me know how to use the 'set based code' for
substituting
> > the cursor usage?
> > Normally I use the cursor to go through a set of rows and do some
actions
> > with the values from each row (like calling another procedure, passing a
> > column value from that row). How can I achieve the same with the 'set
based
> > code'?
> >
> > Arun
> >
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > > SQL Server has a WHILE loop... but cursors, loops and procedural
> > processing
> > > are best avoided. Try to write set-based code for all your data
> > manipulation.
> > > Post here if you need help.
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> >
> >
> >|||Hi David,
I'll let you know the background . you can then comment on the solution...
We are developing a tools suite and we have a concept of
'Projects' in that. All tools work under the context of a 'Project' and we
are giving the facility to import and export the Projects
between two installations of our tools suite. To retrieve the data
corresponding to a specific Project, I don't use any hard coded queries,
since our tool set may grow over a period of time and more tables will be
added that refers to the 'Projects table'.
My logic, traverses through the relationship(FK-PK) that exists between the
tables and collect all the data
that is directly or indirectly related to a given 'Project'. For this
traversing, I've written a procedure that'll be called recursively. Inside
that procedure, for the given table, I get the list of tables that this
table depends on (having Foreign Keys) and for each of them, i do the same
till I reach the end. Like this, I'm building a sequence of tables (whose
data has to be fetched) and then collect the data from them and export that
as XML
So inside the procedure, I collect all the related tables. I collect this
data from the INFORMATION_SCHEMA views
Regards,
Arun
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:3F18A963-CC4F-4412-9271-CA4756441EEB@.microsoft.com...
> > Normally I use the cursor to go through a set of rows and do some
actions
> > with the values from each row (like calling another procedure, passing a
> > column value from that row). How can I achieve the same with the 'set
based
> > code'?
> See the reply I posted in response to Ian. If you *must* invoke a stored
> procedure for each row then yes, you have to use a cursor or equivalent
> procedural loop. But if that SP just performs straight data manipulation
then
> you're probably better off rewriting it so that you can perform the
operation
> once for a whole data set rather than individually for each row. There
isn't
> a generic answer to your question because it depends on what the SP does.
> --
> David Portas
> SQL Server MVP
> --
>
> "Arun" wrote:
> > Hi David,
> >
> > Can you please let me know how to use the 'set based code' for
substituting
> > the cursor usage?
> > Normally I use the cursor to go through a set of rows and do some
actions
> > with the values from each row (like calling another procedure, passing a
> > column value from that row). How can I achieve the same with the 'set
based
> > code'?
> >
> > Arun
> >
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > > SQL Server has a WHILE loop... but cursors, loops and procedural
> > processing
> > > are best avoided. Try to write set-based code for all your data
> > manipulation.
> > > Post here if you need help.
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> >
> >
> >|||Your tools suite sounds like some sort of generic database development tool
or management tool. For that sort of software you will likely require lots of
dynamic SQL and probably plenty of procedural code too because you presumably
know little or nothing about the database schema you'll be working with. From
your description I'm not sure whether it's possible to do any better than
what you have.
I guess my previous reply was really limited to talking about business
process applications, which is what most of us deal with on a daily basis. In
a business process application, the schema should be static and therefore
that sort of problem shouldn't arise.
--
David Portas
SQL Server MVP
--
"Arun" wrote:
> Hi David,
> I'll let you know the background . you can then comment on the solution...
> We are developing a tools suite and we have a concept of
> 'Projects' in that. All tools work under the context of a 'Project' and we
> are giving the facility to import and export the Projects
> between two installations of our tools suite. To retrieve the data
> corresponding to a specific Project, I don't use any hard coded queries,
> since our tool set may grow over a period of time and more tables will be
> added that refers to the 'Projects table'.
> My logic, traverses through the relationship(FK-PK) that exists between the
> tables and collect all the data
> that is directly or indirectly related to a given 'Project'. For this
> traversing, I've written a procedure that'll be called recursively. Inside
> that procedure, for the given table, I get the list of tables that this
> table depends on (having Foreign Keys) and for each of them, i do the same
> till I reach the end. Like this, I'm building a sequence of tables (whose
> data has to be fetched) and then collect the data from them and export that
> as XML
> So inside the procedure, I collect all the related tables. I collect this
> data from the INFORMATION_SCHEMA views
> Regards,
> Arun
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:3F18A963-CC4F-4412-9271-CA4756441EEB@.microsoft.com...
> > > Normally I use the cursor to go through a set of rows and do some
> actions
> > > with the values from each row (like calling another procedure, passing a
> > > column value from that row). How can I achieve the same with the 'set
> based
> > > code'?
> >
> > See the reply I posted in response to Ian. If you *must* invoke a stored
> > procedure for each row then yes, you have to use a cursor or equivalent
> > procedural loop. But if that SP just performs straight data manipulation
> then
> > you're probably better off rewriting it so that you can perform the
> operation
> > once for a whole data set rather than individually for each row. There
> isn't
> > a generic answer to your question because it depends on what the SP does.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> > "Arun" wrote:
> >
> > > Hi David,
> > >
> > > Can you please let me know how to use the 'set based code' for
> substituting
> > > the cursor usage?
> > > Normally I use the cursor to go through a set of rows and do some
> actions
> > > with the values from each row (like calling another procedure, passing a
> > > column value from that row). How can I achieve the same with the 'set
> based
> > > code'?
> > >
> > > Arun
> > >
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > > news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > > > SQL Server has a WHILE loop... but cursors, loops and procedural
> > > processing
> > > > are best avoided. Try to write set-based code for all your data
> > > manipulation.
> > > > Post here if you need help.
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > >
> > >
> > >
>
>|||Hi David,
You are right. At the run time, I do not have any information on the tables.
First I build the list of tables to be considered and then create some
temporary tables corresponding to the ones present in the sequence, then
copy the data (related only to the given project) to those tables and then
query these tables, get the data to the Business logic layer and then
delete the tables that are created temporarily..
Arun
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:17F08EFD-D5D6-4DFF-9570-AC3DDD47A8ED@.microsoft.com...
> Your tools suite sounds like some sort of generic database development
tool
> or management tool. For that sort of software you will likely require lots
of
> dynamic SQL and probably plenty of procedural code too because you
presumably
> know little or nothing about the database schema you'll be working with.
From
> your description I'm not sure whether it's possible to do any better than
> what you have.
> I guess my previous reply was really limited to talking about business
> process applications, which is what most of us deal with on a daily basis.
In
> a business process application, the schema should be static and therefore
> that sort of problem shouldn't arise.
> --
> David Portas
> SQL Server MVP
> --
>
> "Arun" wrote:
> > Hi David,
> >
> > I'll let you know the background . you can then comment on the
solution...
> >
> > We are developing a tools suite and we have a concept of
> > 'Projects' in that. All tools work under the context of a 'Project' and
we
> > are giving the facility to import and export the Projects
> > between two installations of our tools suite. To retrieve the data
> > corresponding to a specific Project, I don't use any hard coded queries,
> > since our tool set may grow over a period of time and more tables will
be
> > added that refers to the 'Projects table'.
> > My logic, traverses through the relationship(FK-PK) that exists between
the
> > tables and collect all the data
> > that is directly or indirectly related to a given 'Project'. For this
> > traversing, I've written a procedure that'll be called recursively.
Inside
> > that procedure, for the given table, I get the list of tables that this
> > table depends on (having Foreign Keys) and for each of them, i do the
same
> > till I reach the end. Like this, I'm building a sequence of tables
(whose
> > data has to be fetched) and then collect the data from them and export
that
> > as XML
> >
> > So inside the procedure, I collect all the related tables. I collect
this
> > data from the INFORMATION_SCHEMA views
> >
> > Regards,
> > Arun
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:3F18A963-CC4F-4412-9271-CA4756441EEB@.microsoft.com...
> > > > Normally I use the cursor to go through a set of rows and do some
> > actions
> > > > with the values from each row (like calling another procedure,
passing a
> > > > column value from that row). How can I achieve the same with the
'set
> > based
> > > > code'?
> > >
> > > See the reply I posted in response to Ian. If you *must* invoke a
stored
> > > procedure for each row then yes, you have to use a cursor or
equivalent
> > > procedural loop. But if that SP just performs straight data
manipulation
> > then
> > > you're probably better off rewriting it so that you can perform the
> > operation
> > > once for a whole data set rather than individually for each row. There
> > isn't
> > > a generic answer to your question because it depends on what the SP
does.
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > > "Arun" wrote:
> > >
> > > > Hi David,
> > > >
> > > > Can you please let me know how to use the 'set based code' for
> > substituting
> > > > the cursor usage?
> > > > Normally I use the cursor to go through a set of rows and do some
> > actions
> > > > with the values from each row (like calling another procedure,
passing a
> > > > column value from that row). How can I achieve the same with the
'set
> > based
> > > > code'?
> > > >
> > > > Arun
> > > >
> > > >
> > > >
> > > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message
> > > > news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > > > > SQL Server has a WHILE loop... but cursors, loops and procedural
> > > > processing
> > > > > are best avoided. Try to write set-based code for all your data
> > > > manipulation.
> > > > > Post here if you need help.
> > > > >
> > > > > --
> > > > > David Portas
> > > > > SQL Server MVP
> > > > > --
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||There are packaged ETL tools that will help you develop and automate these
kinds of processes. Or maybe you are going into competition with the likes of
Ascential and Informatica?!
--
David Portas
SQL Server MVP
--|||I got curious if the following problem can be solved with set-based
operations:
Concatenate the values of a column into one string. (Of course you may
assume that the table isn't too big for the rows to concatenate into a
string of acceptable size.)
As example (don't be picky about the code, I've only written it from
scratch, for demonstration purposes):
CREATE TABLE STRINGS(id int primary key, str varchar(20))
INSERT INTO STRINGS VALUES(1, 'string1')
INSERT INTO STRINGS VALUES(2, 'string2')
INSERT INTO STRINGS VALUES(3, 'string3')
Then, FN_CONCATENATE_FIELDS() should return 'string1string2string3'.
The real-time problem behind this is that I'm trying to dynamically create a
table that has columns the name of which is determined by another table that
has the column names, then fill it with some sum values (unimportant).
I've written a function that generates the 'create table' statement (with
cursors), what is left is to fill the table. For that, I query the columns
of this 'matrix-table' from INFORMATION_SCHEMA.COLUMNS, and create an insert
statement based on that.
I would be glad, of course, to find a set-based solution, which I presume
would be much more efficient.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> SQL Server has a WHILE loop... but cursors, loops and procedural
processing
> are best avoided. Try to write set-based code for all your data
manipulation.
> Post here if you need help.
> --
> David Portas
> SQL Server MVP
> --
>|||You can use
Select @.WholeString = Col1 + Col2 + Col3 from TestTable , where Col1, Col2
and Col3 are the columns of the given table.
If you are creating your tables dynamically and that you'll have the column
information only during the run time, you can create the above query
dynamically and execute that using sp_executesql
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u3RkXMIsEHA.2776@.TK2MSFTNGP14.phx.gbl...
> I got curious if the following problem can be solved with set-based
> operations:
> Concatenate the values of a column into one string. (Of course you may
> assume that the table isn't too big for the rows to concatenate into a
> string of acceptable size.)
> As example (don't be picky about the code, I've only written it from
> scratch, for demonstration purposes):
> CREATE TABLE STRINGS(id int primary key, str varchar(20))
> INSERT INTO STRINGS VALUES(1, 'string1')
> INSERT INTO STRINGS VALUES(2, 'string2')
> INSERT INTO STRINGS VALUES(3, 'string3')
> Then, FN_CONCATENATE_FIELDS() should return 'string1string2string3'.
> The real-time problem behind this is that I'm trying to dynamically create
a
> table that has columns the name of which is determined by another table
that
> has the column names, then fill it with some sum values (unimportant).
> I've written a function that generates the 'create table' statement (with
> cursors), what is left is to fill the table. For that, I query the columns
> of this 'matrix-table' from INFORMATION_SCHEMA.COLUMNS, and create an
insert
> statement based on that.
> I would be glad, of course, to find a set-based solution, which I presume
> would be much more efficient.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > SQL Server has a WHILE loop... but cursors, loops and procedural
> processing
> > are best avoided. Try to write set-based code for all your data
> manipulation.
> > Post here if you need help.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>|||Actually, I do create the string and execute it dynamically. The question
is, how can you create the string without a cursor if you don't know the
names and number of the columns in the table.
("insert into mytable(...) values (...)")
"Arun" <kathir_arun@.hotmail.com> wrote in message
news:OFcvKqNsEHA.2664@.TK2MSFTNGP12.phx.gbl...
> You can use
> Select @.WholeString = Col1 + Col2 + Col3 from TestTable , where Col1, Col2
> and Col3 are the columns of the given table.
> If you are creating your tables dynamically and that you'll have the
column
> information only during the run time, you can create the above query
> dynamically and execute that using sp_executesql
>
>
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:u3RkXMIsEHA.2776@.TK2MSFTNGP14.phx.gbl...
> > I got curious if the following problem can be solved with set-based
> > operations:
> > Concatenate the values of a column into one string. (Of course you may
> > assume that the table isn't too big for the rows to concatenate into a
> > string of acceptable size.)
> > As example (don't be picky about the code, I've only written it from
> > scratch, for demonstration purposes):
> >
> > CREATE TABLE STRINGS(id int primary key, str varchar(20))
> > INSERT INTO STRINGS VALUES(1, 'string1')
> > INSERT INTO STRINGS VALUES(2, 'string2')
> > INSERT INTO STRINGS VALUES(3, 'string3')
> >
> > Then, FN_CONCATENATE_FIELDS() should return 'string1string2string3'.
> >
> > The real-time problem behind this is that I'm trying to dynamically
create
> a
> > table that has columns the name of which is determined by another table
> that
> > has the column names, then fill it with some sum values (unimportant).
> > I've written a function that generates the 'create table' statement
(with
> > cursors), what is left is to fill the table. For that, I query the
columns
> > of this 'matrix-table' from INFORMATION_SCHEMA.COLUMNS, and create an
> insert
> > statement based on that.
> >
> > I would be glad, of course, to find a set-based solution, which I
presume
> > would be much more efficient.
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> > > SQL Server has a WHILE loop... but cursors, loops and procedural
> > processing
> > > are best avoided. Try to write set-based code for all your data
> > manipulation.
> > > Post here if you need help.
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> >
> >
>
>|||There are various solutions for cross-tabs and concatenation across rows.
Here are some links:
http://www.aspfaq.com/2462
Also, Google for "concatenation" in this group to find lots of related
examples.
However, these are really presentational functions for display or printing
in a report. The usual recommendtion is that such functionality should be
done client-side in your application or reporting tool, not in the database.
--
David Portas
SQL Server MVP
--|||lots to think about there thanks david
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88FD9FB0-AFA2-4DC6-883E-9AD8C99B6CCF@.microsoft.com...
> I'd estimate that for most people using SQL Server at least 99.99% of data
> manipulation can be done without a cursor. Inevitably there are inherently
> procedural tasks that are not regular data manipulation and these are what
> I
> would call acceptable uses of a cursor. I'm talking about things like
> sending
> emails and automated management processes such as monitoring databases,
> updating statistics, rebuilding indexes, managing backups, etc.
> Sometimes operational constraints (as distinct from technical ones) may
> compel you to use a cursor where you might be better off without. For
> example
> some existing code in a stored procedure that needs to be invoked for each
> row of a set and where you haven't the remit or the resources to rewrite
> that
> SP.
> There is also a relatively small class of tasks which don't have feasible
> set-based solutions but where a cursor proves more efficient. These are
> rare
> in my experience although one or two examples have been discussed in this
> group and elsewhere. If you think you have encountered one of these then
> always get a second opinion. Post the problem here if you like.
> Finally there is what anecdotal evidence suggests is by far the most
> common
> use of a cursor: those written by developers familiar with procedural
> languages who don't know SQL well enough to write effective set-based
> code.
> There are lots of examples of these. Unfortunately this code is usually
> inefficient, doesn't scale well and is often very costly to support and
> maintain. As a guide, I haven't written a single cursor for an actual
> data-manipulation task in the last 5 years. So if you are writing curors
> (except for admin-type tasks) regularly then you should certainly think
> again
> about whether your code is as efficient, reliable and maintainable as it
> ought to be.
> --
> David Portas
> SQL Server MVP
> --
>|||Their is a big difference between the way that Oracle and SQL Server
implement cursors. This is the root reason that Oracle doesn't discourage
cursors at all and that they are generally discouraged on SQL Server.
Basically, Oracle executes a cursor behind the scenes for every query. This
is called an implicit cursor. Therefore, Oracle queries perform no
differently than Oracle cursors, since they're both the same thing behind
the scenes.
SQL Server, on the other hand, treats queries and cursors as separate
structures, each with different performance qualities, memory usage, and
caching behavior. In SQL Server, cursors require a different space in
memory and have different persistence than queries. That's why you have to
explicitly issue a DEALLOCATE CURSOR statement when you're done using a SQL
Server cursor.
Hope this background helps a bit.
-Kevin
SQL Server MVP
I support the Professional Association for SQL Server (PASS -
www.sqlpass.org).
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E20E1465-2111-491E-856A-ACD237144E94@.microsoft.com...
> SQL Server has a WHILE loop... but cursors, loops and procedural
processing
> are best avoided. Try to write set-based code for all your data
manipulation.
> Post here if you need help.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems to be true that Oracle developers like cursors, even where there
are obvious set-based solutions. Even if cursor performance isn't an issue,
I still find this a little off-putting since, in my experience, set-based
code is usually much cheaper to maintain and support than the equivalent
procedural, cursor-based code. On the other hand if procedural code is
standard practice then maybe good Oracle pros don't have any problems on
that front either. Or maybe it's just that Oracle shops often have bigger
teams with more resources to throw at server-side development. (again this
is just my own experience plus anecdotal evidence).
Another possiblility is that a reliance on non set-based code became the
norm because Oracle's implementation of SQL used to be weak in some areas
(e.g. no ANSI outer join syntax until Oracle 9). Are there good reasons for
Oracle developers to prefer non set-based code post version 9?
--
David Portas
SQL Server MVP
--

Cursor for loops

Sunday, February 19, 2012

CTE - determine closest item

I have included the create table statements, sample data and a stored procedure.

I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.

I have two requests/needs/problems that I need to solve:

(1)

I want to create a NEW stored procedure that takes a WhoID and an AssocID.

This new stored procedure would show the shortest or closest path between WhoID and AssocID.

For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be

6 - 8

8 - 2

2 - 3

3 - 1

(2)

I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.

Please provide your suggestions, comments, or solutions!

CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY

SET IDENTITY_INSERT dbo.Who ON
GO
INSERT INTO Who (WhoID,FName) VALUES (1,'user1')
INSERT INTO Who (WhoID,FName) VALUES (2,'user2')
INSERT INTO Who (WhoID,FName) VALUES (3,'admin1')
INSERT INTO Who (WhoID,FName) VALUES (4,'admin2')
INSERT INTO Who (WhoID,FName) VALUES (5,'admin3')
INSERT INTO Who (WhoID,FName) VALUES (6,'Keith')
INSERT INTO Who (WhoID,FName) VALUES (7,'fred')
INSERT INTO Who (WhoID,FName) VALUES (8,'Joe')
INSERT INTO Who (WhoID,FName) VALUES (9,'Jack')
INSERT INTO Who (WhoID,FName) VALUES (10,'Doug')
INSERT INTO Who (WhoID,FName) VALUES (11,'Mark')
INSERT INTO Who (WhoID,FName) VALUES (12,'Al')
INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff')
INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon')
INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley')
INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa')
INSERT INTO Who (WhoID,FName) VALUES (17,'Julie')
INSERT INTO Who (WhoID,FName) VALUES (18,'Brian')
go
SET IDENTITY_INSERT dbo.Who OFF
GO
INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13)
INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2)
INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17)
INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5)
INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)

go
create proc dbo.procCTE_Assoc
@.WhoID bigint
AS

WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent)
AS
(
-- Anchor Who definition
SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl,
CONVERT(varchar(2000),AssocWho.FName) AS Sort,
CONVERT(varchar(255), AssocWho.FName)
FROM Who M
INNER JOIN Assoc MA ON M.WhoID = MA.WhoID
INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID
WHERE M.WhoID = @.WhoID
UNION ALL
-- Recursive Who definition
SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1,
CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName),
CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName)
FROM Who g1
INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID
INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID
INNER JOIN GroupsCTE CTE
ON MA1.WhoID = CTE.AssocID
)

SELECT *
FROM GroupsCTE
ORDER BY Sort

RETURN (0)
GO

exec procCTE_Assoc 6

To Find the Shortest Path,

Code Snippet

create table #paths (

[from] int ,

[to] int

);

insert into #paths values('1','2');

insert into #paths values('2','3');

insert into #paths values('2','5');

insert into #paths values('3','5');

insert into #paths values('5','6');

insert into #paths values('6','7');

insert into #paths values('7','9');

insert into #paths values('5','9');

declare @.findfrom as int

declare @.findto as int

set @.findfrom = 1

set @.findto = 9

;with cte

(

select

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]

from

#paths

where

[from] = @.findfrom

union all

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))

from

#paths p

join cte c

on p.[from] = c.[to]

and p.[from] <> @.findto

)

, pathordered

(

select * , min(level) over() minpathlevel from cte where path like '%\(' + cast(@.findto as varchar) + ')'

)

--select [path],[nodepath] from pathordered where level=minpathlevel

select p.[from], p.[to] from #paths p join pathordered po

onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'

and po.level=po.minpathlevel

drop table #paths

|||

Wow. That is amazing. Thanks for your help. I wish I knew how it worked. Hopefully I will be able to study your code and try to understand what it does. I modified the code so that it would work against the existing table and I created a proc out of this code. I created another proc where I loaded the results of your query/cte into a temp table and I loaded the results from the cte that I posted into a temp table and I joined the two results (so that I could get the order correct -- Lvl).

Now, does anyone have any idea how to show all nodes / branches where a given item is related to another item (part 2 of my original post)?

Keith

|||

For your Question 2

Code Snippet

create table #paths (

[from] int ,

[to] int

);

insert into #paths values('1','2');

insert into #paths values('2','3');

insert into #paths values('2','5');

insert into #paths values('3','5');

insert into #paths values('5','6');

insert into #paths values('6','7');

insert into #paths values('7','9');

insert into #paths values('5','9');

insert into #paths values('2','10');

declare @.find as int

set @.find = 9

;with cte

(

select

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]

from

#paths

--where

--[from] = @.findfrom

union all

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))

from

#paths p

join cte c

on p.[from] = c.[to]

)

,Finder

(

Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'

)

--select distinct p.[from], p.[to],po.Path from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'order by path

select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'

drop table #paths

|||

With all paths (not only From & To with orignal path),

create table #paths (

[from] int ,

[to] int

);

insert into #paths values('1','2');

insert into #paths values('2','3');

insert into #paths values('2','5');

insert into #paths values('3','5');

insert into #paths values('5','6');

insert into #paths values('6','7');

insert into #paths values('7','9');

insert into #paths values('5','9');

insert into #paths values('2','10');

declare @.find as int

set @.find = 6

;with cte

(

select

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]

from

#paths

union all

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))

from

#paths p

join cte c

on p.[from] = c.[to]

)

,Finder

(

Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'

)

,requiredpaths

(

select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'

)

,CTE2

(

select *

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

from requiredpaths Where [from] not in (Select [to] from requiredpaths)

Union All

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

from

requiredpaths p

join cte2 c

on p.[from] = c.[to]

)

Select * from cte2 order By path

drop table #paths

|||

How do I prevent the following error?

Msg 530, Level 16, State 1, Line 12

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I receive that error when I execute the CTE against this set of data:

set @.findfrom = 7

set @.findto = 21

INSERT INTO #paths ([from],[to])VALUES(1,4)

INSERT INTO #paths ([from],[to])VALUES(2,NULL)

INSERT INTO #paths ([from],[to])VALUES(2,1)

INSERT INTO #paths ([from],[to])VALUES(2,3)

INSERT INTO #paths ([from],[to])VALUES(6,7)

INSERT INTO #paths ([from],[to])VALUES(6,8)

INSERT INTO #paths ([from],[to])VALUES(7,NULL)

INSERT INTO #paths ([from],[to])VALUES(7,20)

INSERT INTO #paths ([from],[to])VALUES(7,22)

INSERT INTO #paths ([from],[to])VALUES(7,26)

INSERT INTO #paths ([from],[to])VALUES(7,28)

INSERT INTO #paths ([from],[to])VALUES(7,35)

INSERT INTO #paths ([from],[to])VALUES(7,38)

INSERT INTO #paths ([from],[to])VALUES(7,44)

INSERT INTO #paths ([from],[to])VALUES(7,48)

INSERT INTO #paths ([from],[to])VALUES(7,49)

INSERT INTO #paths ([from],[to])VALUES(7,50)

INSERT INTO #paths ([from],[to])VALUES(7,51)

INSERT INTO #paths ([from],[to])VALUES(7,52)

INSERT INTO #paths ([from],[to])VALUES(7,53)

INSERT INTO #paths ([from],[to])VALUES(7,54)

INSERT INTO #paths ([from],[to])VALUES(7,55)

INSERT INTO #paths ([from],[to])VALUES(7,56)

INSERT INTO #paths ([from],[to])VALUES(8,2)

INSERT INTO #paths ([from],[to])VALUES(14,8)

INSERT INTO #paths ([from],[to])VALUES(20,21)

INSERT INTO #paths ([from],[to])VALUES(22,23)

INSERT INTO #paths ([from],[to])VALUES(22,31)

INSERT INTO #paths ([from],[to])VALUES(23,25)

INSERT INTO #paths ([from],[to])VALUES(26,27)

INSERT INTO #paths ([from],[to])VALUES(28,29)

INSERT INTO #paths ([from],[to])VALUES(29,30)

INSERT INTO #paths ([from],[to])VALUES(31,32)

INSERT INTO #paths ([from],[to])VALUES(32,34)

INSERT INTO #paths ([from],[to])VALUES(35,36)

INSERT INTO #paths ([from],[to])VALUES(36,37)

INSERT INTO #paths ([from],[to])VALUES(38,39)

INSERT INTO #paths ([from],[to])VALUES(39,0)

INSERT INTO #paths ([from],[to])VALUES(39,40)

INSERT INTO #paths ([from],[to])VALUES(39,41)

INSERT INTO #paths ([from],[to])VALUES(41,NULL)

INSERT INTO #paths ([from],[to])VALUES(41,42)

INSERT INTO #paths ([from],[to])VALUES(42,41)

CTE - determine closest item

I have included the create table statements, sample data and a stored procedure.

I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.

I have two requests/needs/problems that I need to solve:

(1)

I want to create a NEW stored procedure that takes a WhoID and an AssocID.

This new stored procedure would show the shortest or closest path between WhoID and AssocID.

For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be

6 - 8

8 - 2

2 - 3

3 - 1

(2)

Please provide your suggestions, comments, or solutions!

CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY

go
create proc dbo.procCTE_Assoc
@.WhoID bigint
AS

SELECT *
FROM GroupsCTE
ORDER BY Sort

RETURN (0)
GO

exec procCTE_Assoc 6

To Find the Shortest Path,

Code Snippet

create table #paths (

[from] int ,

[to] int

);

insert into #paths values('1','2');

insert into #paths values('2','3');

insert into #paths values('2','5');

insert into #paths values('3','5');

insert into #paths values('5','6');

insert into #paths values('6','7');

insert into #paths values('7','9');

insert into #paths values('5','9');

declare @.findfrom as int

declare @.findto as int

set @.findfrom = 1

set @.findto = 9

;with cte

(

select

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]

from

#paths

where

[from] = @.findfrom

union all

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))

from

#paths p

join cte c

on p.[from] = c.[to]

and p.[from] <> @.findto

)

, pathordered

(

select * , min(level) over() minpathlevel from cte where path like '%\(' + cast(@.findto as varchar) + ')'

)

--select [path],[nodepath] from pathordered where level=minpathlevel

select p.[from], p.[to] from #paths p join pathordered po

onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'

and po.level=po.minpathlevel

drop table #paths

|||

Now, does anyone have any idea how to show all nodes / branches where a given item is related to another item (part 2 of my original post)?

Keith

|||

For your Question 2

Code Snippet

create table #paths (

[from] int ,

[to] int

);

insert into #paths values('1','2');

insert into #paths values('2','3');

insert into #paths values('2','5');

insert into #paths values('3','5');

insert into #paths values('5','6');

insert into #paths values('6','7');

insert into #paths values('7','9');

insert into #paths values('5','9');

insert into #paths values('2','10');

declare @.find as int

set @.find = 9

;with cte

(

select

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]

from

#paths

--where

--[from] = @.findfrom

union all

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))

from

#paths p

join cte c

on p.[from] = c.[to]

)

,Finder

(

Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'

)

--select distinct p.[from], p.[to],po.Path from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'order by path

select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'

drop table #paths

|||

With all paths (not only From & To with orignal path),

create table #paths (

[from] int ,

[to] int

);

insert into #paths values('1','2');

insert into #paths values('2','3');

insert into #paths values('2','5');

insert into #paths values('3','5');

insert into #paths values('5','6');

insert into #paths values('6','7');

insert into #paths values('7','9');

insert into #paths values('5','9');

insert into #paths values('2','10');

declare @.find as int

set @.find = 6

;with cte

(

select

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]

from

#paths

union all

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))

from

#paths p

join cte c

on p.[from] = c.[to]

)

,Finder

(

Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'

)

,requiredpaths

(

select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'

)

,CTE2

(

select *

, cast(0 as int) [level]

, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]

from requiredpaths Where [from] not in (Select [to] from requiredpaths)

Union All

select

p.*

, [level] + 1

, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))

from

requiredpaths p

join cte2 c

on p.[from] = c.[to]

)

Select * from cte2 order By path

drop table #paths

|||

How do I prevent the following error?

Msg 530, Level 16, State 1, Line 12

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I receive that error when I execute the CTE against this set of data:

set @.findfrom = 7

set @.findto = 21

INSERT INTO #paths ([from],[to])VALUES(1,4)

INSERT INTO #paths ([from],[to])VALUES(2,NULL)

INSERT INTO #paths ([from],[to])VALUES(2,1)

INSERT INTO #paths ([from],[to])VALUES(2,3)

INSERT INTO #paths ([from],[to])VALUES(6,7)

INSERT INTO #paths ([from],[to])VALUES(6,8)

INSERT INTO #paths ([from],[to])VALUES(7,NULL)

INSERT INTO #paths ([from],[to])VALUES(7,20)

INSERT INTO #paths ([from],[to])VALUES(7,22)

INSERT INTO #paths ([from],[to])VALUES(7,26)

INSERT INTO #paths ([from],[to])VALUES(7,28)

INSERT INTO #paths ([from],[to])VALUES(7,35)

INSERT INTO #paths ([from],[to])VALUES(7,38)

INSERT INTO #paths ([from],[to])VALUES(7,44)

INSERT INTO #paths ([from],[to])VALUES(7,48)

INSERT INTO #paths ([from],[to])VALUES(7,49)

INSERT INTO #paths ([from],[to])VALUES(7,50)

INSERT INTO #paths ([from],[to])VALUES(7,51)

INSERT INTO #paths ([from],[to])VALUES(7,52)

INSERT INTO #paths ([from],[to])VALUES(7,53)

INSERT INTO #paths ([from],[to])VALUES(7,54)

INSERT INTO #paths ([from],[to])VALUES(7,55)

INSERT INTO #paths ([from],[to])VALUES(7,56)

INSERT INTO #paths ([from],[to])VALUES(8,2)

INSERT INTO #paths ([from],[to])VALUES(14,8)

INSERT INTO #paths ([from],[to])VALUES(20,21)

INSERT INTO #paths ([from],[to])VALUES(22,23)

INSERT INTO #paths ([from],[to])VALUES(22,31)

INSERT INTO #paths ([from],[to])VALUES(23,25)

INSERT INTO #paths ([from],[to])VALUES(26,27)

INSERT INTO #paths ([from],[to])VALUES(28,29)

INSERT INTO #paths ([from],[to])VALUES(29,30)

INSERT INTO #paths ([from],[to])VALUES(31,32)

INSERT INTO #paths ([from],[to])VALUES(32,34)

INSERT INTO #paths ([from],[to])VALUES(35,36)

INSERT INTO #paths ([from],[to])VALUES(36,37)

INSERT INTO #paths ([from],[to])VALUES(38,39)

INSERT INTO #paths ([from],[to])VALUES(39,0)

INSERT INTO #paths ([from],[to])VALUES(39,40)

INSERT INTO #paths ([from],[to])VALUES(39,41)

INSERT INTO #paths ([from],[to])VALUES(41,NULL)

INSERT INTO #paths ([from],[to])VALUES(41,42)

INSERT INTO #paths ([from],[to])VALUES(42,41)

Sunday, March 25, 2012

Cursor for loops?

Cursor for loops?

Cursor for loops

Cursor for loops

Cursor for loops

Cursor for loops

Sunday, February 19, 2012

CTE - determine closest item

CTE - determine closest item

Cursors vs. Queries

Blog Archive

About Me