Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Unanswered: Whats wrong in this query ?

    DECLARE @Filter nvarchar(2048)
    DECLARE @EventName nvarchar(2048)
    DECLARE @Ticker nvarchar(2048)
    DECLARE @Cusip nvarchar(2048)

    SET @EventName = 'DIVIDEND'
    SET @Ticker = 'IBM'
    SET @Cusip = ''

    SET @Filter = 'Name like ''' + @EventName + '%'' AND Ticker Like ''' + @Ticker + '%'' AND Cusip like ''' + @Cusip + '%'''

    Select * from eventdatadetails
    where @Filter


    I want to execute the above SELECT statement and the filters in the where clause should come from the @Fitler variable... but query analyzer gives an error. If i replace the data in the @Filter with the variable in the query, everything works fine ...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're making this wayyyyyyy to hard for yourself!
    There's no need for your filter when you can already compare it all in the where clause.
    (Plus you have far to many apostrophes - see how they're set out below)
    Code:
    SELECT *
    FROM eventdatadetails
    WHERE Name Like '%' + @EventName + '%'
    AND Ticker Like '%' + @Ticker + '%'
    AND Cusip Like '%' + @Cusip + '%'
    Let me know how that goes

    - GeorgeV
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    I have to use the Filter part 10 times in a UDF. So i would rather have it in a variable ... Pls @filter might not be known until run time.

    As a temporary workaround i already have what you have recommended. Thanks !!

    For the apostrophe , they are to be escaped.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Without knowing all the details of what you're trying to do...

    I'd suggest dynamic SQL:

    Code:
    DECLARE @Filter nvarchar(2048)
    DECLARE @EventName nvarchar(2048)
    DECLARE @Ticker nvarchar(2048)
    DECLARE @Cusip nvarchar(2048)
    DECLARE @SQLSTRING nvarchar(500)
    
    SET @EventName = 'DIVIDEND'
    SET @Ticker = 'IBM'
    SET @Cusip = ''
    
    SET @Filter = 'Name like ''' + @EventName + '%'' AND Ticker Like ''' + @Ticker + '%'' AND Cusip like ''' + @Cusip + '%''' 
    
    SET @SQLSTRING='SELECT * FROM EVENTDETAILS WHERE ' + @Filter
    
    EXEC (@SQLSTRING)
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Since you're declaring your variables as static - they're in effect hard coded...
    So why not simply put:
    Code:
    DECLARE @Filter nvarchar(2048)
    DECLARE @EventName nvarchar(2048)
    DECLARE @Ticker nvarchar(2048)
    DECLARE @Cusip nvarchar(2048)
    --Can't be bothered collouring the code anymore..
    SET @EventName = 'DIVIDEND'
    SET @Ticker = 'IBM'
    SET @Cusip = ''
    
    SET @Filter = 'Name like 'DIVIDEND%' AND Ticker Like 'IBM%' AND Cusip = ''
    Well, because they're hard coded you could simply run an SQL statment minus your variables...

    But yeah, if these are going to be given different values form another source... Why not try:
    Code:
    DECLARE @EventName nvarchar(2048)
    SET @EventName = '%' + 'DIVIDEND' + '%'
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •