Post reply

temp table and dynamic sql problem

  • April 17, 2011 at 12:10 pm

    #238614

    i created a store proc

    i created a temp table and data is passed using another temp table

    now i am using dynamic sql and calling the temp table and it is not working.

    but when i am passing the same values in dynamic sql instead of temp table it is working .

    any ideas ?

    brief description:

    temp table a : 'a','b'

    temp table b : insert into b select from a

    then using dynamic sql:

    select * from x inner join y where z in (select * from temptable b) -- not working

    select * from x inner join y where z in('a','b') -- working

    any ideas? pls help

  • April 17, 2011 at 12:24 pm

    #1312524

    Use single column name againts (*)

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS|MCITP|OCA|OCP|OCE|SCJP|IBMCDBA

    My Blog

    http://www.aureus-salah.com

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • April 17, 2011 at 12:26 pm

    #1312526

    actually i used single column

  • April 17, 2011 at 5:30 pm

    #1312567

    Would you post:

    1. how are you creating the temp table.

    2. the actual dynamic sql you are using to reference it

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 18, 2011 at 7:21 am

    #1312739

    pardeshkumar (4/17/2011)

    i created a store proc

    i created a temp table and data is passed using another temp table

    now i am using dynamic sql and calling the temp table and it is not working.

    but when i am passing the same values in dynamic sql instead of temp table it is working .

    any ideas ?

    brief description:

    temp table a : 'a','b'

    temp table b : insert into b select from a

    then using dynamic sql:

    select * from x inner join y where z in (select * from temptable b) -- not working

    select * from x inner join y where z in('a','b') -- working

    any ideas? pls help

    Your problem has nothing to do with dynamic sql. You CANNOT use a SELECT * in an IN clause. You must specify a single column name there, say IN (SELECT field1 from temptableb).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 18, 2011 at 7:34 am

    #1312762

    TheSQLGuru (4/18/2011)

    Your problem has nothing to do with dynamic sql. You CANNOT use a SELECT * in an IN clause. You must specify a single column name there, say IN (SELECT field1 from temptableb).

    Whats wrong with below code

    create table #Temp (t int)

    create table #Temp1 (t1 int)

    insert into #Temp values (1),(5),(8)

    insert into #Temp1 values (1),(5),(4)

    Select * from #Temp where t in(

    Select * from #Temp1 )

    Drop table #Temp

    Drop table #Temp1

    it will going to work fine since it is having only one column(#Temp1).

    Thanks
    Parthi

  • April 18, 2011 at 7:50 am

    #1312782

    Yup, but as soon as you do this then you're screwed.

    create table #Temp (t int)

    create table #Temp1 (t1 int, fails int)

    insert into #Temp values (1)

    insert into #Temp1 values (1)

    Select * from #Temp where t in(

    Select * from #Temp1 )

    Drop table #Temp

    Drop table #Temp1

    Why code for failure when it takes 2 seconds to make sure it works forever?

  • April 18, 2011 at 8:03 am

    #1312805

    It is a very good practice to have column name even though there is a single column ,I just said because TheSQLGuru has stated that it will not work.In order to show him that, if there is a single column we can have * over there, but in real time we must specify column which is good practice so that followers will be able to understand.

    Thanks
    Parthi

  • April 18, 2011 at 8:06 am

    #1312811

    parthi-1705 (4/18/2011)

    It is a very good practice to have column name even though there is a single column ,I just said because TheSQLGuru has stated that it will not work.In order to show him that, if there is a single column we can have * over there, but in real time we must specify column which is good practice so that followers will be able to understand.

    Agreed, but I can't remember the last time I use a temp table with only 1 column in prod... hence always seems like a nice argument to have :w00t:.

  • April 18, 2011 at 8:13 am

    #1312820

    Ninja's_RGR'us (4/18/2011)

    Agreed, but I can't remember the last time I use a temp table with only 1 column in prod... hence always seems like a nice argument to have :w00t:.

    ha ha 😛 😀 😛 😀

    I too agree that use of single column to temp table is not a good one,instead we can have a join or some related conditions for taking single column.I just say that we can also use * for single column 😀 😀

    Thanks
    Parthi

  • April 18, 2011 at 8:19 am

    #1312825

    All thanks for your help .

    Problem is resolved.Problem is datatype difference and i changed and it worked fine.

    Once again thanks for all your help

  • April 18, 2011 at 11:50 am

    #1312993

    pardeshkumar (4/18/2011)

    All thanks for your help .

    Problem is resolved.Problem is datatype difference and i changed and it worked fine.

    Once again thanks for all your help

    Sure woulda been nice if you had given us table create scripts to begin with! 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 20, 2011 at 3:34 am

    #1313806

    post removed, hadn't seen it was solved..

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply