can table variables used to join with physical tbl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

can table variables used to join with physical tbl

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-28 : 01:57:48

can i use table variable to map with other tables to get the record.
something like this
delcare @mytbl name
{id int
name varchar(50)
}
insert into @mytbl values(1,'name')
select name from @mytbl a
inner join MyPhysicaltbl b
on a.name = b.name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 02:00:18

Yes. Do you encounter any problem with this ?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-28 : 02:03:04

declare @mytbl table(id int,
name varchar(50)
)

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 02:05:22

i need a bigger screen

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-28 : 02:07:16

quote:
Originally posted by khtan

Yes. Do you encounter any problem with this ?

KH

im not sure is it b'coz of that. i have SP in which i have table variable which holds some records..
my physical table query maynot return any value sometimes.
this SP when i execute in the query analyser it is not giving me any error..but when i try to run from the front end it is saying "Error converting datatype varchar to bigint" but if i comment the table variable, i dont get this error...
but this is also happening only for certian date...so im confused

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 02:17:25

Does your SP accept any input parameters ?
What is the value pass in from the front end ?
Will you be able to simulate the error in Query Analyser using the same parameter value as the front end ?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-28 : 02:25:24

date?

the format of this date data in your table is int like 20060428?
and you're passing the value from now()?

or the value you are trying to pass is not valid?

verify the date formats in BOL

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-28 : 04:11:49

Post the code you used in sp and show us how you executed if from front end application. Also you need to use DateTime datatype than Varchar(if you used it)

Madhivanan

Failing to plan is Planning to fail

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-01 : 10:38:49

quote:
"Error converting datatype varchar to bigint" but if i comment the table variable, i dont get this error...
but this is also happening only for certian date

When I've seen that in the past there has always been a 'bad' value in the table. It sounds like your table variable forces a conversion that simply rejects the bad data, whereas selecting directly from the table didn't doesn't the conversion and so your bad data stays hidden.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources