none
Select records using dynamic condition

    Question

  • Hi,

    I have query with dynamic conditions by passing the values  into a variable and using them in the query

    SELECT * FROM dbo.table WHERE ID > ? and ID <= ?

    This condition is not pulling all the records, if I have 1,2,3,4,5 records in my source which I'm running using ETL, the above condition is missing some values.

    using ">" and "<=", are good or should I use between?

    Please let me know.

    Saturday, June 29, 2013 2:33 AM

Answers

  • using ">" and "<=", are good or should I use between?

    Please let me know.

    You know better than anyone else that you need between OR"<" and ">". It depends on your requirement.

    Between is inclusive of boundary values and is equivalent of <= and >=.


    Nitesh Rai- Please mark the post as answered if it answers your question

    Monday, July 01, 2013 7:43 AM
  • Hi,

    Before you use the query in SSIS packages , can you check in SSMS query window and execute and check the results manually by passing the values and then based on that you decide which query to use.

    In your case if you need all the records from 1 to 5 then see the below query 

    SELECT * FROM dbo.Table WHERE ID> = 1 AND ID <= 5
    
    SELECT * FROM dbo.Table WHERE ID> = ? AND ID <= ?

    Regards

    Naveen

    • Marked as answer by SQL_Gun Monday, July 22, 2013 6:21 PM
    Tuesday, July 16, 2013 6:53 AM

All replies

  • If your first ? = 1 and the second ? = 5 you will get 2,3,4,5, so yea you will not pick ID = 1. Did you try the BETWEEN? Since these are variables you need to ensure the values you think you are passing are the ones the code executes.

    Hope this is Helpfull. J Alverson Microsoft BI Solutions Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, June 30, 2013 7:08 PM
  • Why not >= and <=?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 01, 2013 7:05 AM
  • using ">" and "<=", are good or should I use between?

    Please let me know.

    You know better than anyone else that you need between OR"<" and ">". It depends on your requirement.

    Between is inclusive of boundary values and is equivalent of <= and >=.


    Nitesh Rai- Please mark the post as answered if it answers your question

    Monday, July 01, 2013 7:43 AM
  • This is not working,

    My where condition as follows:

    Variable1: SELECT MAX(PropertyID) Dest_PropertyID FROM dbo.Table_Dest

    Variable2: SELECT MAX(PropertyID) Source_PropertyID FROM dbo.Table_Source

    SELECT * FROM dbo.Table_Source
    WHERE PropertyID > ?(Dest_PropertyID) AND PropertyID <=?(Source_PropertyID)

    With the above sql query, its missing some records, is there anything I need to make changes?

    Please let me know, this is killing my complete day.

    Tuesday, July 16, 2013 2:33 AM
  • Hi,

    Before you use the query in SSIS packages , can you check in SSMS query window and execute and check the results manually by passing the values and then based on that you decide which query to use.

    In your case if you need all the records from 1 to 5 then see the below query 

    SELECT * FROM dbo.Table WHERE ID> = 1 AND ID <= 5
    
    SELECT * FROM dbo.Table WHERE ID> = ? AND ID <= ?

    Regards

    Naveen

    • Marked as answer by SQL_Gun Monday, July 22, 2013 6:21 PM
    Tuesday, July 16, 2013 6:53 AM