Problem executing procedure and how can I use their results in a select statement

I have this Stored Procedure that walk through a table that stores hierarchical data and reorganize the output, so the resultset will be ordered by this hierarchy.

The table structure is (fieldnames in english between parentheses for better comprehension):

CD_CATEGORIA (CD_CATEGORY)
DS_CATEGORIA (DS_CATEGORY)
CD_CATEGORIAMAE (CD_MOTHERCATEGORY)

Here is the Stored Procedure code:

CREATE PROCEDURE [dbo].[sp_RetornaCategorias]

-- Add the parameters for the stored procedure here

@.ID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @.TabelaSaida table(

cd_categoria int,

ds_categoria varchar(70),

nr_nivel int);

declare @.i int

select @.i = 0

-- keep going until no more rows added

while @.@.rowcount > 0

begin

select @.i = @.i + 1

insert @.TabelaSaida

-- Get all children of previous level

select Categorias.cd_categoria, Categorias.ds_categoria, @.i + 1

from Categorias, @.TabelaSaida AS TblSaida

where nr_nivel = @.i

and Categorias.cd_categoriamae = TblSaida.cd_categoria

end

-- Saída de dados

-- output with hierarchy formatted

select space((nr_nivel-1)*4) + ds_categoria

from @.TabelaSaida

order by nr_nivel

END

But when I try to execute this Stored Procedure, it runs but nothing is returned.

I'm using this code to execute it:

EXEC [dbo].[sp_RetornaCategorias]

@.ID = 1

Are there anything wrong with it? How can I fix this?

And how can I call a Stored Procedure and get its resultset from a SELECT statement?

Hi Juliano,

Your SP wont actually return anything unless you declare a Variable as OUTPUT.

Your SP's resultset is from the select statement.

There is great MSDN documentation on SP's here; http://msdn2.microsoft.com/en-us/netframework/aa479373.aspx

With regards to fixing it, im not entirely sure its broken yet.

|||

It looks like your insert into the tablevariable is based in a join against that same tablevariable, but when you start out, it's newly created and thus empty..
So, the insert would then yield 0 rows, and the loop will break.

Try to run the SQL statements in a query window, then you can see what happens in each step.

/Kenneth

|||

ur procedure and calling seems to be alright...just check the data in the tyables ur refering...and is there actually nething to be returned.......basically run the select query seperately and check..

this 1...does this gives ne values ?

select Categorias.cd_categoria, Categorias.ds_categoria

from Categorias, @.TabelaSaida AS TblSaida

and Categorias.cd_categoriamae = TblSaida.cd_categoria

|||

I don't see how this could possibly return any rows, since @.TableSaida that is used in the join is newly declared and created, and thus is also empty.

/Kenneth