Post reply

Hello friend, exists some way that a procedure return an array,or cursor,or recordset ?

  • September 22, 2005 at 3:55 pm

    #89068

    Hello friends, i need that a procedures it returns me a result of a query,or some array that i can track it

    well i thought that with the new var of type table it could do it, but it failed

    for example   create procedure SPReturnarray 

                      @array  table ( element varchar (255) ) as ................

     

    but it didnt work, it casts Incorrect syntax,  well actually i return the result of a query to transform it to a string.

    exists another way to do that work??

    thanks to read me... Good Hunt......

                       

     

  • September 22, 2005 at 5:20 pm

    #592447

    Try these two links for Arrays in SQL Server, I am assuming you know Arrays and Strings are classes but SQL Server types are Structures, the former are reference types and the later are value types.   Hope this helps.

    http://www.bizdatasolutions.com/tsql/sqlarrays.asp

    http://www.sommarskog.se/arrays-in-sql.html

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

  • September 23, 2005 at 12:06 am

    #592485

    Use hash tables.

    Create TABLE #array () in procedure from where you call SPReturnarray and fill it with data.

    Than you can select from #array inside SPReturnarray.

     

    _____________
    Code for TallyGenerator

  • September 23, 2005 at 3:52 am

    #592536

    Functions in SQL Server 2000/2005 can return tables.

    For example:

    CREATE FUNCTION dbo.udf_ReturnsTable()
    RETURNS @tblTest
    TABLE([Value] int)
    AS
    BEGIN
            -- Insert some values
            INSERT INTO @tblTest([Value]) VALUES(1)
            INSERT INTO @tblTest([Value]) VALUES(2)
            INSERT INTO @tblTest([Value]) VALUES(3)
            INSERT INTO @tblTest([Value]) VALUES(4)
    RETURN
    END
    GO
    -- Test this function
    SELECT * FROM dbo.udf_ReturnsTable()

  • September 23, 2005 at 4:15 am

    #592539

    As I understand problem was not in treturning array from SP but in supplying array to SP.

    You cannot do it with parameters, but you can use #Table.

    _____________
    Code for TallyGenerator

  • September 23, 2005 at 4:35 am

    #592542

    Reads to me like Hector wants the Procedure to return the data.  Also I'm assuming that we are returning data to a T-SQL routine or stored procedure.

    Another way of doing it would be to EXEC the results of a proc into a temp table, e.g.

    INSERT INTO #mytable EXEC dbo.spReturnArray

    Where spReturnArray outputs using a Select, e.g.

    CREATE PROCEDURE dbo.spReturnArray

    AS

    -- Just a quick select to output some values

      SELECT 1 AS [Value]

      UNION SELECT 2 AS [Value]

      UNION SELECT 3 AS [Value]

      UNION SELECT 4 AS [Value]

    GO

    If you wanted to input the value to the Stored Proc then you could fill a table, or a temp table or a global temp table, depending on whether you were creating the table in or out of process, and Select the values in the proc.

     

  • September 23, 2005 at 12:50 pm

    #592725

    Thank you everybody i read your genial idea and the articles, they are very interesting thank you for your helps, you are smart people

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply