lop deleted row

Is it possible to find who was deleted row from sql server 2000 with t-sql,
query analyzer?No. but you can use third-party tools:
www.lumigent.com
www.red-gate.com
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Nikolami" <nikolami@.nyc.com> wrote in message
news:dv3qam$40u$1@.ss405.t-com.hr...
Is it possible to find who was deleted row from sql server 2000 with t-sql,
query analyzer?|||After the fact? Not really, SQL Server doesn't track that information. You
may be able to recover some of the transaction details by using a log
reader, e.g. see http://www.aspfaq.com/2449
"Nikolami" <nikolami@.nyc.com> wrote in message
news:dv3qam$40u$1@.ss405.t-com.hr...
> Is it possible to find who was deleted row from sql server 2000 with
> t-sql, query analyzer?
>|||Not if you don=B4t save that information somewhere, or you use a
logreader for this.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks guys in advice

loops oops

Hi,
How can I walk through a result of a select query? I have two tables.
TableA(id, name1, name2, notes), TableB(id, grade). I have to bulk insert
some data, (given: name1, name2, notes and grande too). The "bulk insert"
inserts datas in one step. So first I insert the values a temporary table
(TableTmp). Then I have to walk through this TableTmp and at each row I
have to do this:
1, insert name1, name2, notes into TableA
2, get back the actual @.@.identity
3, insert grade into TableB where is the last identity.
I would like to make it in sql query analizer...
I have problem with the loop. I have no idea how to make it.
Is it not overcomplicated? Is there any simple solution?
Thank you for help
chris
Message posted via http://www.webservertalk.comI don't think you'll need a loop. Usually it's best to avoid loops and row
by row processing.
Unfortunately you haven't given us any clues about keys or constraints or
shown us the table where this data comes from. So here is a wild guess:
INSERT INTO TableA (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM Unspecified
INSERT INTO TableB (id, col4, col5, col6)
SELECT DISTINCT
A.id /* the IDENTITY col from A */ ,
U.col4, U.col5, U.col6
FROM Unspecified AS U
JOIN TableA AS A
ON U.col1 = A.col1
AND U.col2 = A.col2
AND U.col3 = A.col3
David Portas
SQL Server MVP
--|||Mary,
To me it sounds like your database design is a bit odd.
You have the details of a student(?) in TableA, and the grade that the
student got in TableB. Presumably if you actually have:
TableA (students): id, name1, name2, notes...
TableC (subjects): id, code, description...
TableB (grades): studentid, subjectid, grade
then you could import your data into a temporary table... and then make sure
that all your subjects and students are listed:
insert into TableA (name1, ...)
select t.name1, ...
from TableTemp t
where not exists (select * from TableA a where a.name1 = t.name1 and a.name2
= t.name2 and ...)
(and similar for TableC)
And then insert the grades:
insert into TableC (studentid, subjectid, grade)
select stud.id, subj.id, t.grade
from TableTemp t JOIN TableA stud on stud.name1 = t.name1 and stud.name2...
JOIN TableC subj on subj.code = t.code ...
The '...' are the other fields and stuff that you're looking to identify
students by.
Of course, it's much nicer to have a separate file of "studentcode",
"subjectcode", "grade", if you can fetch the details like that.
Rob|||Thanx.
It seems working..Thank you very much

LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.

> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql

LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

sql

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping through results of a OSQL query

Hello,

I have the following problem.

I wrote a batch file that runs a sql script on SQLServer 2000. This
script must be executed on several databases. This works fine.

The problem is that in my database is a table that holds a databases i
have to update with this script. What i want is run a query with osql
in my batchfile to retrieve this these records so I can loop through
them an run the script for those databases.

I managed to execute the query that return the records and write them
to a textfile.

What i want is store the results in some kind of a resultset so i can
loop through them in my batchfile.

Anyone have the solution

Thanks in advance

Patrickpatrick (pveechoud@.xs4all.nl) writes:
> I have the following problem.
> I wrote a batch file that runs a sql script on SQLServer 2000. This
> script must be executed on several databases. This works fine.
> The problem is that in my database is a table that holds a databases i
> have to update with this script. What i want is run a query with osql
> in my batchfile to retrieve this these records so I can loop through
> them an run the script for those databases.
> I managed to execute the query that return the records and write them
> to a textfile.
> What i want is store the results in some kind of a resultset so i can
> loop through them in my batchfile.

A simple-minded approach is to write the query so that it generates
a complete OSQL command and you write that to the file. Then you
execute the file.

But it would probably better to write this in some script language
like VBscript or Perl from which you can submit queries.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

looping through query result column and PRINT to log file....

i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!

Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file...

Thanks in advanceI got it :)

For any1 who might be interested heres how its done

DECLARE @.NbrList VarChar(300)
SELECT @.NbrList = COALESCE(@.NbrList + ', ', '') + CAST(Invoice AS varchar(30))
from TRANSACTIONS where ProductID = 'CVSDBN'
PRINT @.NbrList

Thanks ;)sql

Looping through list in a query

Hello,
I am trying to write a query that will execute the same command to each
database in a list. More specifically I use the following command to get a
list of all the user-defined databases on the server:
SELECT catalog_name from information_schema.Schemata
WHERE NOT (CATALOG_NAME in
('tempdb','master','msdb','model','Northwind','pub s'))
and now I want to perfrom that same action (ie dettach or check for orphan
users etc) on each DB that I get from this query. Is there a way to do this
in SQL?
thanks
christos
Christos Kritikos wrote:
> Hello,
> I am trying to write a query that will execute the same command to
> each database in a list. More specifically I use the following
> command to get a list of all the user-defined databases on the server:
> SELECT catalog_name from information_schema.Schemata
> WHERE NOT (CATALOG_NAME in
> ('tempdb','master','msdb','model','Northwind','pub s'))
> and now I want to perfrom that same action (ie dettach or check for
> orphan users etc) on each DB that I get from this query. Is there a
> way to do this in SQL?
> thanks
> christos
Checking user information can be done in sysprocesses. No need to
enumerate the databases. If you really need a way to run the same
command against each database, you can use xp_MSForEachDB or just use a
temp table and interate through the results.
David Gugick
Imceda Software
www.imceda.com

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GOIf running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GO
If running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>
sql

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GOIf running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>

Looping Query

I know I have done this before but for the life of me I can remember how'
When I run the query below it loops and loops, how do I make it appear just
once - missing something but brain freeze has taken over aaaggghhh...
select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
[transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '27 September,2004' and '28 September, 2004'
your help much appreciatedWhat do you mean by "The Query loops" ? A query doesn=B4t loop around on
its own, actually it doesn=B4t loop at all.|||same information in rows 1-84 shows up in query analyzer then row 85 shows
row 1 again then so on until row 829227262525......or until system stops
"Jens" wrote:

> What do you mean by "The Query loops" ? A query doesn′t loop around on
> its own, actually it doesn′t loop at all.
>|||examnotes (Ivo@.discussions.microsoft.com) writes:
> I know I have done this before but for the life of me I can remember how'
> When I run the query below it loops and loops, how do I make it appear
> just once - missing something but brain freeze has taken over
> aaaggghhh...
> select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
> [transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '27 September,2004' and '28 September, 2004'
You have two tables in your WHERE clause, but there is no condition for
joining them, which means that you get call possible combination of
rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
rows in transactions for the given time range, the query will produce
one milliard rows.
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|||Erland Sommarskog skrev:

> You have two tables in your WHERE clause, but there is no condition for
> joining them, which means that you get call possible combination of
> rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
> rows in transactions for the given time range, the query will produce
> one milliard rows.
>
That would be one 'billion' in english, or are there even differences
between british and american english? (Not to mention Erland's swedish
english ;) )
/impslayer, aka Birger Johansson|||impslayer (impslayer@.hotmail.com) writes:
> That would be one 'billion' in english, or are there even differences
> between british and american english?
See http://www.m-w.com/dictionary/milliard.
However, both in British and American English, they capitalise "British",
"American" and "English". :-)
But maybe we should table this discussion before it goes too far. (Now,
let's see how many how get *that* one!)
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|||Erland Sommarskog skrev:

> impslayer (impslayer@.hotmail.com) writes:
> See http://www.m-w.com/dictionary/milliard.
>
When will I ever (EVER!) learn to check stuff out before I make a
post?
Had no idea 'milliard' existed in English (sic!), and was hoping I'd
clarify that you meant 'billion'... Definitely no cheap shot, I promise
:)
/impslayer, aka NoBrainPoster|||impslayer (impslayer@.hotmail.com) writes:
> When will I ever (EVER!) learn to check stuff out before I make a
> post?
> Had no idea 'milliard' existed in English (sic!), and was hoping I'd
> clarify that you meant 'billion'... Definitely no cheap shot, I promise
>:)
Rest assured that if I ever say "billion", I mean a real big billion, and
not the wimpy billions they have over there!
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

Looping in a query

Hello Everybody,

I have a requirement where the query needs to looped.

I have a table f553002A. In this table i have iykit,iyitm as columns.

for every iykit,there can be a set of iyitm

like this

select KIT, ITM from F553002A where KIT = 11758

KIT ITM

11758 11494

11758 11705

11758 11713

11758 11868

Again for every ITM as KIT in the same table ,there can have set of values(ITM) like this

select KIT, ITM from F553002A where KIT = 11713 (parent’s ITM)

KIT ITM

11713.0 2917.0
11713.0 2910.0
11713.0 5013.0
11713.0 2890.0
11713.0 2873.0
11713.0 2843.0
11713.0 2856.0
11713.0 2836.0
11713.0 2879.0
11713.0 7974.0
11713.0 2789.0
11713.0 2842.0
11713.0 2915.0
11713.0 2885.0
11713.0 2908.0
11713.0 2858.0
11713.0 2871.0
11713.0 2841.0

the same with 11494,11705,11868.

Again 2917.0 (ITM) in the above example as KIT can have another set of ITM's in the same table

I want all the END ITM's along with there Grand parent KIT and Immediate Parent Kit returned in a result set.

For Ex

11758 11713.0 2917.0

....

...

..

.

11758 11494 ...........................

........

...

..

.

However i know i can achieve this with Stored procdeure. I want this to be implemented in SQL Query where i can embeed it in a VBscript

Please help me in writing the query for this. Thanks in advance

We are using sql server 2000. not 2005

here you go..

Code Snippet

Create Table #f553002a (

[KIT] int ,

[ITM] int

);

Insert Into #f553002a Values(1,2);

Insert Into #f553002a Values(1,3);

Insert Into #f553002a Values(1,4);

Insert Into #f553002a Values(1,5);

Insert Into #f553002a Values(2,6);

Insert Into #f553002a Values(3,8);

Insert Into #f553002a Values(4,9);

Insert Into #f553002a Values(5,10);

Insert Into #f553002a Values(6,11);

Insert Into #f553002a Values(7,12);

Insert Into #f553002a Values(8,13);

Insert Into #f553002a Values(9,14);

Insert Into #f553002a Values(10,2);

Insert Into #f553002a Values(11,3);

;With CTE(KIT,ITM,Level)

as

(

Select KIT,ITM,1 Level from #f553002a Where KIT = 3 --@.StartKitValue

Union ALL

Select Child.KIT,Child.ITM,Main.Level + 1 From CTE Main

Join #f553002a Child On Main.Itm = Child.Kit

)

Select KIT,ITM From CTE Order By Level

|||

Can any body through a light why the query below is returning syntax error

With CTE(IYKIT,IYITM,Level)

as

(

Select IYKIT,IYITM,1 Level from f553002a Where IYKIT = 11758 --@.StartKitValue

Union ALL

Select Child.IYKIT,Child.IYITM,Main.Level + 1 From CTE Main

Join f553002a Child On Main.IYItm = Child.IYKit

)

Select IYKIT,IYITM From CTE Order By Level

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'With'.

I am running this on sql server 2005 Management studio.

How ever in my question,i requested for a solution which can work in SQL server 2000, but this works only in sql server 2005

|||

CTE only work with SQL Server 2005. You missed the semicolon ; before the WITH.

In 2000 you can use recursive sp call... let me try this..

sql

looping a query

Can anyone give me a hand on how to loop this query until there are no results left, this is going to be part of a stored procedure by the way.

Code Snippet

declare @.strcharid varchar(21)

set @.strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @.strcharid)
update userdata set loyalty = '0' where struserid = @.strcharid

Ken:

Are you using SQL Server 2000 or SQL Server 2005?

|||sql 2000 now, the programs i need to run do not work with 2005 unfortuantely.
|||

My first pass at this might be something like this:

Code Snippet

update tb_user
set strauthority = '255',
BanReason = 'Master Skill Hack',
TermDate = current_timestamp
from tb_user a
join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
)
from userData p
where p.class = '108'
and p.authority = '1'
) b
on a.strAccountId = b.strAccountId

update userdata
set loyalty = '0'
where class = '108'
and authority = '1'

I don't feel real good about this solution; will somebody please check me?

|||it returns the error "Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'b'."

my current thought is this, i just need a way to make it rerun until @.count = 0

Code Snippet

CREATE PROCEDURE [MasterHack]
as

begin tran
declare @.strcharid varchar(21)
set @.strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')
declare @.count int
set @.count = (select count(1) struserid from userdata where class = '108' and authority = '1')

if @.count > 0
begin

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @.strcharid)
update userdata set loyalty = '0' where struserid = @.strcharid
end

rollback tran
GO

|||

Thank you, Ken.

Change this:

Code Snippet

join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
)
from userData p
where p.class = '108'
and p.authority = '1'
) b

to this:

Code Snippet

join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
) as strAccountId
from userData p
where p.class = '108'
and p.authority = '1'
) b

Also, I understand that in your original post that this is not the style of code that you sought; however, Transact SQL is in general a set-based language and works far better if you devise set-based processes rather than record-based processes. My response is an attempt to steer your solution to a set-based solution rather than a record-based solution.

Also, if you are going to be writing stored procedures or functions it is good to get into the habbit of writing set-based or set-oriented solutions rather than record-oriented solutions. Set-oriented / set-based solitions are the preferred choice frequently discussed in the MSDN Transact SQL forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Hopefully, other memembers will come forwared with other forum recommendations.

|||Thank you, is there any site you would suggest for this style?
sql

Loop through Query

Out of one messed up table I need to create two related tables. I am stuck in trying to get 'some' of the columns from the messed up table to the new table.

How can I select only the columns I need and insert them in the new table?

Thanks in advance!

Hi There,

I done something similar to thi before and i found this article is helpful.

http://www.sql-server-performance.com/dp_no_cursors.asp

loop through few table in sp

Hi,
I have a lot monthly tables, in order to query some data for last few month,
I have to check a few tables. And the table names are determined by today's
date. If I find the record, I don't need to query other tables anymore. how
can I do it in store procedure? ThanksIf the table names are based on the current date, you'll need to use dynamic
SQL for your query.
Like this:
declare @.today datetime
set @.today = getdate()
declare @.qry nvarchar(1000)
set @.qry = 'select * from sometable_' +
replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
sp_executesql @.qry
But of course, if you want to look through a bunch of tables and stop
searching, you might want to consider populating a temporary table with the
tables you want to check (search through sysobjects for them perhaps), and
then grab the top record, delete it out of your temporary table, search
through the table, and if you find the data, drop out of your while loop. If
you run out of tables to check, you're done and you haven't found it.
But if it's a fixed list of tables, you could just write it out in full,
with return statements appropriately placed.
Hope this helps,
Rob
"Jen" wrote:

> Hi,
> I have a lot monthly tables, in order to query some data for last few mont
h,
> I have to check a few tables. And the table names are determined by today'
s
> date. If I find the record, I don't need to query other tables anymore. ho
w
> can I do it in store procedure? Thanks|||Jen
Can you create a view with an UNION ALL clause to combine those tables and
then query the view BETWEEN required dates?
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
> Hi,
> I have a lot monthly tables, in order to query some data for last few
> month,
> I have to check a few tables. And the table names are determined by
> today's
> date. If I find the record, I don't need to query other tables anymore.
> how
> can I do it in store procedure? Thanks|||thanks. I need to query myTable20060523 first, if record found then I am
done; otherwise I need to continue query myTable200604, myTable200603...,
etc. up to 6 tables. how can I loop through these tables? Thanks
"Rob Farley" wrote:
> If the table names are based on the current date, you'll need to use dynam
ic
> SQL for your query.
> Like this:
> declare @.today datetime
> set @.today = getdate()
> declare @.qry nvarchar(1000)
> set @.qry = 'select * from sometable_' +
> replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
> sp_executesql @.qry
>
> But of course, if you want to look through a bunch of tables and stop
> searching, you might want to consider populating a temporary table with th
e
> tables you want to check (search through sysobjects for them perhaps), and
> then grab the top record, delete it out of your temporary table, search
> through the table, and if you find the data, drop out of your while loop.
If
> you run out of tables to check, you're done and you haven't found it.
> But if it's a fixed list of tables, you could just write it out in full,
> with return statements appropriately placed.
> Hope this helps,
> Rob
>
> "Jen" wrote:
>|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||can I create view in the store procedure? Is there any side effect or
performance issue? How about more than one user is executing the same
procedure?Thanks
"Uri Dimant" wrote:

> Jen
> Can you create a view with an UNION ALL clause to combine those tables and
> then query the view BETWEEN required dates?
>
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
>
>|||--Try something like this:
declare @.qry nvarchar(2000)
declare @.found bit
set @.found = 0
declare @.done bit
set @.done = 0
declare @.tablenames table (id int identity(1,1), name varchar(128));
insert into @.tablenames
select top 6 name
from sysobjects
where name like 'mytable%'
order by 1 desc
declare @.tablename varchar(128)
declare @.tableid int
while (@.found = 0 and @.done = 0)
begin
select top 1 @.tablename = name, @.tableid = id
from @.tablenames
order by id
if (@.@.rowcount = 0)
begin
set @.done = 1
end
else --search through the table
begin
delete from @.tablenames where id = @.tableid
set @.qry = 'declare @.misc int; select @.misc = id from ' + @.tablename + '
where somecol = 15' --This won't return a value, but will set @.@.rowcount
exec sp_executesql @.qry
if (@.@.rowcount > 0)
begin
set @.found = 1
end
end
end
-- Look at the values of @.found and @.tablename to see if you found it, and
what table you found it in
if (@.found = 1)
begin
select @.tablename
end
"Jen" wrote:
> thanks. I need to query myTable20060523 first, if record found then I am
> done; otherwise I need to continue query myTable200604, myTable200603...,
> etc. up to 6 tables. how can I loop through these tables? Thanks
> "Rob Farley" wrote:
>sql

loop in stored procedure?

I have a situation where there is one table with parent and child nodes
i.e.
catID, ParentID
i would like to make a query to find out if an entry resides in a certain
category.
for instance, say i have
Root
--Category 1
--Child 1A
--Child 1B
--Child 1C
--Child 1Ca
if i know the CatID for the Child 1Ca node, how can i write a query that is
able to tell me that it's ultimtely a child of the root nde (Category 1)?
Keep in mind that Category 1 is not always the root node in the tree.Rob,
thanks for your help. this is exactly what i needed.
best wishes.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>