Re: Stored Procedure to return table?

Jack Turnbull wrote:

Hi,
Am new to Stored Procedures and am lost how to achieve the following. I have this table:-

CREATE TABLE [dbo].[docs] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Link] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

I want to write a stored procedure to return a single column table. The first field should contain the result of:

SELECT ParentID WHERE ID = @id

@id being the procedure input parameter.

The procedure should then iterate through the table returning subsequent (single column) rows containing the result of:

SELECT ParentID WHERE ID = @PreviousRowParentID

Until NULL is returned

Can anyone help?

Yes.
</pedant>

declare @ancestor_id int, @candidate_id int
set @ancestor_id = @id
set @candidate_id = @id
while @candidate_id is not null
begin
set @candidate_id = (
select ParentID
from table
where id = @ancestor_id
)
if @candidate_ancestor_id is not null
begin
set @ancestor_id = @candidate_id
end
end
.

Relevant Pages

  • Stored Procedure to return table?
    ... Am new to Stored Procedures and am lost how to achieve the following. ... I want to write a stored procedure to return a single column table. ... SELECT ParentID WHERE ID = @PreviousRowParentID ...
    (comp.databases.ms-sqlserver)
  • Re: Intercepting invalid parameters to a stored procedure
    ... the stored procedures for them. ... @OrderNumber int output ... @Qty int = 1 ...    INSERT INTO Order (CustomerName, ProductId, Qty) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: how to decrypt an encrypted stored proc in 2005
    ... Suppose that the stored procedures are needed exclusively from a particular ... DECLARE @intProcSpace bigint ... DECLARE @procNameLength int ... declare @BasePos int ...
    (microsoft.public.sqlserver.security)
  • Re: stored procedure return value question
    ... sqlserver stored procedures return result sets from a select, and a int ... direction of type ReturnValue. ... ExecuteScaler returns the value of the first column, of the first row, of ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Stored Procedure Error
    ... Replicating stored procedures that use double-quotes instead of single ... The code to support this is quite substantial so I doubt it will ... > @CustID int= Null, ...
    (microsoft.public.sqlserver.replication)