September 22, 2005 at 3:55 pm
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
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
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
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
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
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
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