WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''')
Entire SPROC
------------------------------
CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net 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 Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.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_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GOI see what you're trying to do here - and it will only work with dynamic SQL.
You want to construct a query in the form of:
select ... from ... where Symbol in ('A', 'B', 'C', 'D', 'E')
from a string of 'A B C D E'
You'll need to do this:
declare @.Query varchar(8000)
declare @.Search varchar(100)set @.Search = 'A B C D E'
set @.Query = 'select ... from ... where Symbol in (''' + replace(@.Search, ' ', ''', ''') + ''')'exec(@.Query)
Have fun. Just one more thing though, if you can post me your table definitions, I can help you normalise them. They're not normalised from what I can see in your query above.|||post me your table definitions
how do I do that?|||Is this what you mean?
CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS
declare @.Query varchar(8000)
declare @.Search varchar(1000)
set @.Search = 'SELECT Portfolio_Symbols
FROM a_Users_Portfolios
WHERE (UserID = ''' + @.UserID + ''') AND (Portfolio_Name = ''' + @.Portfolio_Name + ''')'
set @.Query = 'SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net 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 Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.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_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN (''' + replace(@.Search, ' ', ''', ''') + ''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = ''2003 Q3'')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal'
exec(@.Query)
GO|||No, I mean post me your tables - their names, and columns.
e.g.
TableName (Col1 DataType, Col1 DataType, ...)
...|||imsmart.info
LOL|||Hmm, I'm getting more and more confused as to what your inputs are (e.g. what is an example value of @.Symbol) so I'm going to give up. You get the idea, though? With the dynamic query?|||LOL with what?|||This problem is related to your non-normalised database. Normalise your DB and the whole issue will disappear.
