Help with query (using variables)

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

General SQL Server Forums

New to SQL Server Programming

Help with query (using variables)

Author

Topic

Vickyinrhyme
Starting Member

4 Posts

Posted - 2009-07-29 : 15:13:36

For some reason I need a query like this
SET QUOTED_IDENTIFIER OFF
Declare @ValStartDate DateTime,@ValEndDate DateTime,@SecIds Varchar(300)
Set @ValStartDate = '2009-02-25'
Set @ValEndDate = '2009-02-26'
Set @SecIds = "'" + '637844309' + "'" + ',' + "'" + 'IJECE' + "'" + ',' + "'" + 'IJECF' + "'" + ',' + "'" + '887147114' + "'" + ',' + "'" + 'G7127P142' +"'" + ',' + "'" + '863346102' + "'" + ',' + "'" + '8318K4107' + "'"
Select @SecIds
Use EYISP40
Select Distinct
Sec.SecurityId As [Security ID],SecType.TypeName As [Security Type],
PriceList.PriceSourceName As [Independent Pricing Source],
Sec.IssuerDescription As [Pricing Source Description],PriceList.PriceDate As [Pricing Date],
PriceList.price As [Unit Price],PriceList.ExchangeName As [Securities Exchange]
From Security Sec
Inner Join SecurityType SecType On Sec.TypeID = SecType.TypeID
Left Join
(Select t.PriceDate,PrSrce.PriceSourceName,PriceHist1.price,t.SecurityID,Exch.ExchangeName
From (Select SecurityId,PriceDate,Min(PriceHist.PriceSourceID) AS source
From PriceHistory PriceHist Where PriceDate Between @ValStartDate and @ValEndDate GROUP BY PriceDate,SecurityID) t
INNER JOIN PriceHistory PriceHist1
On t.PriceDate = PriceHist1.PriceDate
AND t.source = PriceHist1.PriceSourceID
And PriceHist1.SecurityID = t.SecurityId
And PriceHist1.PriceDate Between @ValStartDate and @ValEndDate
Inner Join PriceSource PrSrce
On t.source = PrSrce.PriceSourceID
Inner Join Exchange Exch
On PriceHist1.ExchangeID = Exch.ExchangeID
)PriceList On PriceList.SecurityID = Sec.SecurityID
Where Sec.TypeID in
(Select TypeID from dbo.SecurityType Where ClassID In
(Select ClassID From dbo.SecurityClass Where ClassName In ('Equities - US')))
And Sec.SecurityID In (@SecIds)
And PriceList.PriceSourceName is Not Null

But the @SecIds variable is not replacing its value correctly (the query result shows no record but infact there is record).

Please help me

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 15:41:55

I think you need to use dynamic SQL for this...something like below.

DECLARE  @strIDs VARCHAR(300) 

SET @strIDs = '''637844309''' + ',' + '''IJECE''' 

SELECT @strIDs 

DECLARE  @SQL VARCHAR(1000) 

SELECT @SQL = 'SELECT * FROM Security ' 

SELECT @SQL = @SQL + 'WHERE SecurityID in (' + @strIDs + ')' 

EXEC( @SQL)

Vickyinrhyme
Starting Member

4 Posts

Posted - 2009-07-29 : 15:46:29

will try this and let know. thanks for the idea

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources