Hello!
We have a bill of materials table with a classic parent/child relationships.
What I need to be able to do is to take a specific part number and return
the highest level parent for that part number.
For instance, If part number A is used as a component in part number B, and
part number B is then used in another component called C, then the highest
level parent for part number A is C.
so the result of the query when ran against part number A would be C
How can I set up such a query?
Thanks
JoeBelow is a solution using a user-defined function. If this
is a frequent requirement, you may want to consider alternate
ways of modeling your hierarchy. If you search groups.google.co.uk
or www.google.com for hierarchy+itzik+sqlserver you'll find some nice
ideas.
-- Original thread at http://groups.google.co.uk/groups?q=A9B05D_C5E784
CREATE TABLE Employee (
pk int not null primary key,
parent int
)
go
INSERT INTO Employee VALUES (1,NULL)
INSERT INTO Employee VALUES (2,NULL)
INSERT INTO Employee VALUES (3,1)
INSERT INTO Employee VALUES (4,2)
INSERT INTO Employee VALUES (5,4)
go
CREATE FUNCTION rootPK(
@.pk INT
) RETURNS INT
AS
BEGIN
DECLARE @.parent INT, @.this INT
SET @.this = NULL
SET @.parent = @.pk
WHILE @.parent IS NOT NULL BEGIN
SELECT
@.this
= pk
, @.parent
= parent
FROM
Employee
WHERE
pk
= @.parent
END
RETURN @.this
END
go
ALTER TABLE Employee ADD rootPK as dbo.rootPK(pk)
go
SELECT * FROM Employee
go
-- drop table Employee
-- drop function dbo.rootPK
-- Steve Kass
-- Drew University
Joe Williams wrote:
> Hello!
> We have a bill of materials table with a classic parent/child relationship
s.
> What I need to be able to do is to take a specific part number and return
> the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B, an
d
> part number B is then used in another component called C, then the highest
> level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>|||There is also a good example at:
http://msdn.microsoft.com/library/d...r />
_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <Joe@.anywhere.com> schrieb im Newsbeitrag
news:%234YOY0jWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have a bill of materials table with a classic parent/child
> relationships. What I need to be able to do is to take a specific part
> number and return the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B,
> and part number B is then used in another component called C, then the
> highest level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>
Showing posts with label classic. Show all posts
Showing posts with label classic. Show all posts
Monday, March 12, 2012
Tuesday, February 21, 2012
Returning multiple values in recordset form
Hope you can help.
Classic ASP / SQL Server 2000
I'd like my proc to return a recordset with 3 columns.
The values of these 3 columns are each generated by a very
different SQL statement.
I cannot figure out how to write all this so it returns a
nice recordset...
Here's the code -- any suggestions will be greatly appreciated.
CREATE PROCEDURE proc_GetStats
(
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @.SQLA nvarchar(2500)
DECLARE @.SQLB nvarchar(2500)
DECLARE @.SQLC nvarchar(2500)
DECLARE @.INTA int
DECLARE @.INTB int
DECLARE @.INTC int
--Those are actually 3 really different select statements
SET @.SQLA = 'SELECT from TABLE_A returns integer'
SET @.SQLB = 'SELECT from TABLE_B returns integer'
SET @.SQLC = 'SELECT from TABLE_C returns integer'
exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
--How to return this in a recordset form?
SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
procEnd:
END
GO
****** What I currently get from running this *************
13556
0
3
INTA INTB INTC
-- -- --
0 0 0Terry,
What do you mean by "a nice recordset"?
If you want to return scalar values to your client app, it is better to
return them as output parameters instead returning them in a recordset.
ADO Performance Best Practices
http://msdn.microsoft.com/library/d.../>
estprac.asp
Best Practices for Using ADO.NET
http://msdn.microsoft.com/library/d.../>
netbest.asp
AMB
"Terry" wrote:
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> (
> )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Your SELECT statements need to assign the scalar result. Otherwise, the
results are returned to the client. For example:
SET @.SQLA = 'SELECT @.INTA = MyColumn FROM TABLE_A'
SET @.SQLB = 'SELECT @.INTB = MyColumn FROM TABLE_B'
SET @.SQLC = 'SELECT @.INTC = MyColumn FROM TABLE_C'
Hope this helps.
Dan Guzman
SQL Server MVP
"Terry" <a165287@.yahoo-youknow.com> wrote in message
news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> ( )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Thanks Dan, I had already tried that and it returns errors:
Must declare the variable '@.INTA'
Must declare the variable '@.INTB'
Must declare the variable '@.INTC'
which makes sense since in the context of the string, the
variable is not declared.
Any other ideas will be appreciated. BTW, the reason I'd
like this returned in recordset form is for compatibility
with existing code. Thanks for your understanding.
Original :: Dan Guzman :: 2005-03-15 08:58
> Your SELECT statements need to assign the scalar result. Otherwise, the
> results are returned to the client. For example:
> SET @.SQLA = 'SELECT @.INTA = MyColumn FROM TABLE_A'
> SET @.SQLB = 'SELECT @.INTB = MyColumn FROM TABLE_B'
> SET @.SQLC = 'SELECT @.INTC = MyColumn FROM TABLE_C'
>|||Is there any way you can type the select statements directly and not use
sp_executesql?
If you use a statement like
SELECT @.INTA=column FROM TableA
It doesn't return a result set and therefore won't be sent back to the ASP
page as a recordset. The very simple queries you gave in your example
certainly don't need to be executed as dynamic SQL.
If you can't, you can use the NextRecordset method of the Recordset object.
WARNING - this is undesirable if you can do ANYTHING AT ALL to avoid it.
Recordset objects are expensive to create.
Ex:
...snip...
Set myRS = myCommand.Execute() 'Opens the recordset created from retrieving
IntA
Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
retrieving IntB
Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
retrieving IntC
Set myRS = myRS.NextRecordset() 'Here you are!
"Terry" <a165287@.yahoo-youknow.com> wrote in message
news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> (
> )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Thanks! That did the trick :)
Original :: CMBergin :: 2005-03-16 08:54
> Is there any way you can type the select statements directly and not use
> sp_executesql?
> If you use a statement like
> SELECT @.INTA=column FROM TableA
> It doesn't return a result set and therefore won't be sent back to the ASP
> page as a recordset. The very simple queries you gave in your example
> certainly don't need to be executed as dynamic SQL.
> If you can't, you can use the NextRecordset method of the Recordset object
.
> WARNING - this is undesirable if you can do ANYTHING AT ALL to avoid it.
> Recordset objects are expensive to create.
> Ex:
> ...snip...
> Set myRS = myCommand.Execute() 'Opens the recordset created from retrievin
g
> IntA
> Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
> retrieving IntB
> Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
> retrieving IntC
> Set myRS = myRS.NextRecordset() 'Here you are!
> "Terry" <a165287@.yahoo-youknow.com> wrote in message
> news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
>
Classic ASP / SQL Server 2000
I'd like my proc to return a recordset with 3 columns.
The values of these 3 columns are each generated by a very
different SQL statement.
I cannot figure out how to write all this so it returns a
nice recordset...
Here's the code -- any suggestions will be greatly appreciated.
CREATE PROCEDURE proc_GetStats
(
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @.SQLA nvarchar(2500)
DECLARE @.SQLB nvarchar(2500)
DECLARE @.SQLC nvarchar(2500)
DECLARE @.INTA int
DECLARE @.INTB int
DECLARE @.INTC int
--Those are actually 3 really different select statements
SET @.SQLA = 'SELECT from TABLE_A returns integer'
SET @.SQLB = 'SELECT from TABLE_B returns integer'
SET @.SQLC = 'SELECT from TABLE_C returns integer'
exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
--How to return this in a recordset form?
SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
procEnd:
END
GO
****** What I currently get from running this *************
13556
0
3
INTA INTB INTC
-- -- --
0 0 0Terry,
What do you mean by "a nice recordset"?
If you want to return scalar values to your client app, it is better to
return them as output parameters instead returning them in a recordset.
ADO Performance Best Practices
http://msdn.microsoft.com/library/d.../>
estprac.asp
Best Practices for Using ADO.NET
http://msdn.microsoft.com/library/d.../>
netbest.asp
AMB
"Terry" wrote:
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> (
> )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Your SELECT statements need to assign the scalar result. Otherwise, the
results are returned to the client. For example:
SET @.SQLA = 'SELECT @.INTA = MyColumn FROM TABLE_A'
SET @.SQLB = 'SELECT @.INTB = MyColumn FROM TABLE_B'
SET @.SQLC = 'SELECT @.INTC = MyColumn FROM TABLE_C'
Hope this helps.
Dan Guzman
SQL Server MVP
"Terry" <a165287@.yahoo-youknow.com> wrote in message
news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> ( )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Thanks Dan, I had already tried that and it returns errors:
Must declare the variable '@.INTA'
Must declare the variable '@.INTB'
Must declare the variable '@.INTC'
which makes sense since in the context of the string, the
variable is not declared.
Any other ideas will be appreciated. BTW, the reason I'd
like this returned in recordset form is for compatibility
with existing code. Thanks for your understanding.
Original :: Dan Guzman :: 2005-03-15 08:58
> Your SELECT statements need to assign the scalar result. Otherwise, the
> results are returned to the client. For example:
> SET @.SQLA = 'SELECT @.INTA = MyColumn FROM TABLE_A'
> SET @.SQLB = 'SELECT @.INTB = MyColumn FROM TABLE_B'
> SET @.SQLC = 'SELECT @.INTC = MyColumn FROM TABLE_C'
>|||Is there any way you can type the select statements directly and not use
sp_executesql?
If you use a statement like
SELECT @.INTA=column FROM TableA
It doesn't return a result set and therefore won't be sent back to the ASP
page as a recordset. The very simple queries you gave in your example
certainly don't need to be executed as dynamic SQL.
If you can't, you can use the NextRecordset method of the Recordset object.
WARNING - this is undesirable if you can do ANYTHING AT ALL to avoid it.
Recordset objects are expensive to create.
Ex:
...snip...
Set myRS = myCommand.Execute() 'Opens the recordset created from retrieving
IntA
Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
retrieving IntB
Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
retrieving IntC
Set myRS = myRS.NextRecordset() 'Here you are!
"Terry" <a165287@.yahoo-youknow.com> wrote in message
news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> (
> )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Thanks! That did the trick :)
Original :: CMBergin :: 2005-03-16 08:54
> Is there any way you can type the select statements directly and not use
> sp_executesql?
> If you use a statement like
> SELECT @.INTA=column FROM TableA
> It doesn't return a result set and therefore won't be sent back to the ASP
> page as a recordset. The very simple queries you gave in your example
> certainly don't need to be executed as dynamic SQL.
> If you can't, you can use the NextRecordset method of the Recordset object
.
> WARNING - this is undesirable if you can do ANYTHING AT ALL to avoid it.
> Recordset objects are expensive to create.
> Ex:
> ...snip...
> Set myRS = myCommand.Execute() 'Opens the recordset created from retrievin
g
> IntA
> Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
> retrieving IntB
> Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
> retrieving IntC
> Set myRS = myRS.NextRecordset() 'Here you are!
> "Terry" <a165287@.yahoo-youknow.com> wrote in message
> news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
>
Subscribe to:
Posts (Atom)