Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Question Unanswered: Comma Separated String which I would like to pass to the IN Predicate

    I have a Comma Separated String which I would like to pass to the IN Predicate I tried the following but it doesn't work.

    ------------------------------------
    CREATE PROCEDURE dbo.<proc name>

    @Components varchar(100)

    AS

    SELECT * FROM <TABLE> WHERE
    <FIELD_NAME> IN (@Components)
    ------------------------------------

    @Components = "1,2,3,4"
    ----
    I have used temp tables and it was working fine.
    But as per our requirement we should not use temp tables or working tables or dynamic queries

    Any ideas that will help me a lot...

    Thanks,
    Murthy

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Murthy SRC
    SELECT * FROM <TABLE> WHERE
    <FIELD_NAME> IN (@Components)
    ------------------------------------

    @Components = "1,2,3,4"

    Any ideas that will help me a lot...
    I think the following could work but it goes without saying that RDBMS's work best when all the data is in 3NF (@Components isn't in 1NF). Out of curiosity can I ask the reasoning behind not being allowed to use temp tables?

    Code:
    select @Components = "," + @Components + ","
    
    select * from <TABLE> 
    WHERE @Components like "%," + <FIELD_NAME> + ",%"

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    First of all, Thanks for sharing your thoughts
    The above query is not working, as i need my query to be in the below manner
    Select * from <Table> Where <Field_Name> IN('1','2','3','4')
    @Components = "1,2,3,4" - This should go to the IN clause

    Below is the reason for not using temp tables.

    we are writing this code in a stored procedure. the stored procedure will be called by a JCAPS application. when we use temp tables, JCAPS is throwing an error saying we cannot create ddl statements in a transaction. for overcoming this we have to turn on the
    sp_dboption "tempdb","ddl in tran",true
    Our client is not allowing to turn on, because of the below reason.

    Warning!

    Data definition language (DDL) commands hold locks on system tables such as sysobjects. Avoid using them inside transactions; if you must use them, keep the transactions short.

    Using any DDL commands on tempdb within transactions may cause your system to grind to a halt. Always leave ddl in tran set to false in tempdb.

    This was given in the link
    Reference Manual Volumes 1 - 4 (Online Only)

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Murthy SRC
    First of all, Thanks for sharing your thoughts
    The above query is not working, as i need my query to be in the below manner
    Select * from <Table> Where <Field_Name> IN('1','2','3','4')
    The query I supplied should produce exactly the same results - why can't you use that?

    Quote Originally Posted by Murthy SRC
    we are writing this code in a stored procedure. the stored procedure will be called by a JCAPS application. when we use temp tables, JCAPS is throwing an error saying we cannot create ddl statements in a transaction. for overcoming this we have to turn on the
    sp_dboption "tempdb","ddl in tran",true
    Our client is not allowing to turn on
    If you want to use temporary tables but can't then just create a permanent table and use a unique id (the process id?) to distinguish between concurrent users. Remember to delete that users data at the end of the sproc. The functionality will be the same as a temporary table but you won't need any DDL in the sproc.

  5. #5
    Join Date
    Jan 2009
    Posts
    6
    Quote Originally Posted by mike_bike_kite
    The query I supplied should produce exactly the same results - why can't you use that?


    If you want to use temporary tables but can't then just create a permanent table and use a unique id (the process id?) to distinguish between concurrent users. Remember to delete that users data at the end of the sproc. The functionality will be the same as a temporary table but you won't need any DDL in the sproc.
    Thank you very much... the query given by you is working fine.
    I came to know one new way of writing query. Thanks a lot..

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Note that the where like '%.. query will always perform a table scan and as your data grows so will your execution time increase.

Posting Permissions

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