Passing an array to sql

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 thi
s
'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'I was given the answer.
I just needed to remove the IN function in the WHERE clause.
========================================
=====
"a" wrote:

> 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 t
his
> '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'v
e
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My Procedur
e --
> 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.Peri
od
> 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.CustomerI
D
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>|||> writtem my procedure. I think the line in question is my last 'Join'
> statement.
I suspect you problem is the WHERE clause:

> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
It looks to me that this an artifact of a previous incorrect technique and
is superseded by your join to the table-valued function. Also, it looks
like you are using SQL 2005 since I see varchar(MAX). In that case, you
might consider passing XML. Untested example:
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols xml)
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
JOIN a_Financials ON
a_Hyperlinks.Yahoo_Main = a_Financials.Yahoo_Main
JOIN a_Industry ON
a_Financials.Industry = a_Industry.Industry
JOIN a_Sector ON
a_Financials.Sector = a_Sector.Sector
JOIN a_Quarter_Index ON
a_Financials.Period = a_Quarter_Index.Period
JOIN a_Name_Symbol ON
a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN (SELECT CAST(PortfolioSymbols.PortfolioSymbol.query('.') AS char(5)) AS
PortfolioSymbol
FROM @.PortfolioSymbols.nodes('/PortfolioSymbols/PortfolioSymbol/text()')
PortfolioSymbols(PortfolioSymbol)) AS PortfolioSymbols ON
a_Name_Symbol.Symbol = PortfolioSymbols.PortfolioSymbol
ORDER BY a_Name_Symbol.Name
GO
EXEC get_company_names_iter '<PortfolioSymbols>
<PortfolioSymbol>ALFKI</PortfolioSymbol>
<PortfolioSymbol>BONAP</PortfolioSymbol>
<PortfolioSymbol>CACTU</PortfolioSymbol>
<PortfolioSymbol>FRANK</PortfolioSymbol>
</PortfolioSymbols>'
Hope this helps.
Dan Guzman
SQL Server MVP
"a" <a@.discussions.microsoft.com> wrote in message
news:B6AF913C-74FE-4214-BBA1-768ED66ADD67@.microsoft.com...
>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'
>