"Jarrod Morrison" <jarrodm@ihug.com.au> wrote in message
news:bnb1r5$qpm$1@lust.ihug.co.nz...
> Hey Simon
>
> Thanks for your help and advice with the stored procedures. I have changed
> the code u gave me a little to do what i needed. The procedure does what i
> need ok but i was wondering how to output this data. I have other stored
> procedures where i specify that the variable is for output but im not sure
> what type of variable to be using (IE im using VarChar at the moment, is
> there an array type ?). At the moment i get the grid showing the results
of
> the query in sql query analyzer but i would like to output it somehow so
> that when i am using the stored procedure with my external program i can
use
> the data from this stored procedure.
>
> Thanks again for all your help
>
>
> /*
> ** Determine Entity Group Memberships
> */
>
> CREATE PROCEDURE [dbo].[EntityStartup]
>
> @MachineName VarChar(50),
> @UserName VarChar(50),
>
> AS
>
> DECLARE @MachineLength Char(2) /* Local Machine Name Length */
> DECLARE @MachInt Char(1) /* Machine Integer Counter */
>
> SET @MachInt = 1
>
> SELECT @MachineLength = Len(@MachineName)
>
> CREATE TABLE #GrpMem (GrpName varchar(50), AuthID varchar(50))
>
> WHILE @MachInt <= @MachineLength
>
> BEGIN
> INSERT INTO #GrpMem (GrpName, AuthID) SELECT * FROM GrpMachines WHERE
> MachineGrp LIKE LEFT(@MachineName,@MachInt)
> SET @MachInt = @MachInt + 1
> END
>
> SELECT * FROM #GrpMem
>
> GO
>

<snip>

That all depends how your application calls the procedure. If you're using
ADO, for example, you'd get the result set (ie the rows you see in QA) into
a recordset object, and do something with it there. Other client libraries
(ODBC, JDBC etc.) will have their own way of handling it.

You can't use a table variable as an output parameter from a stored proc, so
if you need to pass the result set to another stored proc, then you could
look at using a table-valued UDF instead, as discussed here (among other
approaches):

http://www.algonet.se/~sommar/share_data.html


Simon