temp table and dynamic sql problem

Question

Post reply

temp table and dynamic sql problem

pardeshkumar

SSC-Addicted

Points: 482
More actions
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

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

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 1

Use single column name againts (*)

Regards,

Syed Jahanzaib Bin Hassan

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

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

pardeshkumar SSC-Addicted Points: 482 More actions · Answer 2

pardeshkumar

SSC-Addicted

Points: 482

April 17, 2011 at 12:26 pm

#1312526

actually i used single column

WayneS SSC Guru Points: 95460 More actions · Answer 3

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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?

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

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:.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 9

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

pardeshkumar SSC-Addicted Points: 482 More actions · Answer 10

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

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

Willem G SSC Eights! Points: 809 More actions · Answer 12

Willem G

SSC Eights!

Points: 809

April 20, 2011 at 3:34 am

#1313806

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