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.
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)) ASSELECT 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 TA1FROM 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.nstrWHERE (a_Name_Symbol.Symbol IN (@PortfolioSymbols))ORDER BY a_Name_Symbol.Name------------------------------------------ iter_charlist_to_table Function -- List-of-stringsHere 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 ENDHere 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.nstrWHERE (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 OptimizerTG |
|
|
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.nstrWHERE (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 OptimizerTG
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|