SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 can table variables used to join with physical tbl
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sarakumar
Posting Yak Master

Singapore
108 Posts

Posted - 04/28/2006 :  01:57:48  Show Profile  Reply with Quote
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)

Singapore
17601 Posts

Posted - 04/28/2006 :  02:00:18  Show Profile  Reply with Quote
Yes. Do you encounter any problem with this ?



KH


Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/28/2006 :  02:03:04  Show Profile  Send jen a Yahoo! Message  Reply with Quote
declare @mytbl table(id int,
name varchar(50)
)



--------------------
keeping it simple...
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 04/28/2006 :  02:05:22  Show Profile  Reply with Quote
i need a bigger screen



KH


Go to Top of Page

Sarakumar
Posting Yak Master

Singapore
108 Posts

Posted - 04/28/2006 :  02:07:16  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 04/28/2006 :  02:17:25  Show Profile  Reply with Quote
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 ?



KH


Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/28/2006 :  02:25:24  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/28/2006 :  04:11:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 05/01/2006 :  10:38:49  Show Profile  Reply with Quote
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."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000