Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Passing an array to sql

Author  Topic 

kloepper
Yak Posting Veteran

77 Posts

Posted - 2006-03-04 : 10:03:55
I haven't done sql in a year so I could use some help here. I've got a procedure below that uses the Function (listed under it here) that is supposed to parse a string and pass the parsed string as an array to sql. I'm not doing something correctly. If I pass in a single Symbol (string) my procedure returns what it is supposed to, but if I pass in a string like this 'A,B,C' nothing is returned, as though there is no parsing taking place.

The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also works when I run the example, so there must be some mistake in the way I've writtem my procedure. I think the line in question is my last 'Join' statement.

Anyone have any ideas?

Thanks,
Paul
===========


------------------------------------------------------------- My Procedure --
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@PortfolioSymbols NvarChar(max)) AS

SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector, a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income, a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
INNER JOIN
a_Financials ON a_Hyperlinks.Yahoo_Main = a_Financials.Yahoo_Main
INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry
INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector
INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN
iter_charlist_to_table(@PortfolioSymbols, DEFAULT) s ON a_Name_Symbol.Symbol = s.nstr

WHERE (a_Name_Symbol.Symbol IN (@PortfolioSymbols))
ORDER BY a_Name_Symbol.Name



------------------------------------------ iter_charlist_to_table Function --

List-of-strings

Here is a similar function, but that returns a table of strings.

CREATE FUNCTION iter_charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END

SET @leftover = @tmpstr
END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END

Here is an example on how you would use the function:

CREATE PROCEDURE get_company_names_iter @customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Customers C
JOIN iter_charlist_to_table(@customers, DEFAULT) s ON C.CustomerID = s.nstr
go
EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-04 : 11:28:03
quote:

JOIN
iter_charlist_to_table(@PortfolioSymbols, DEFAULT) s ON a_Name_Symbol.Symbol = s.nstr

WHERE (a_Name_Symbol.Symbol IN (@PortfolioSymbols))


looks like your JOIN and WHERE clauses are redundant. The iter_charlist_to_table function is used correctly in your JOIN and that is all you need. The usage of a comma seperated list of symbols with an IN () will not work (with more than one value). See the sticky topic in "new to sql server" forum in the section "CSV / Splitting delimited lists
"

Be One with the Optimizer
TG
Go to Top of Page

kloepper
Yak Posting Veteran

77 Posts

Posted - 2006-03-04 : 11:55:35
O'Mighty TG, your Wisdom must only be surpassed by your Goodness!

It worked.

Thank you Much for the Help,

Paul

================================================

quote:
Originally posted by TG

quote:

JOIN
iter_charlist_to_table(@PortfolioSymbols, DEFAULT) s ON a_Name_Symbol.Symbol = s.nstr

WHERE (a_Name_Symbol.Symbol IN (@PortfolioSymbols))


looks like your JOIN and WHERE clauses are redundant. The iter_charlist_to_table function is used correctly in your JOIN and that is all you need. The usage of a comma seperated list of symbols with an IN () will not work (with more than one value). See the sticky topic in "new to sql server" forum in the section "CSV / Splitting delimited lists
"

Be One with the Optimizer
TG

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 01:05:44
Also search for Where in @MYCSV here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -