|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Copy SQL ResultSet to Variablevariable (I think similar in the way one can pass a resultset back from a UFN), so one doesn't have to keep on querying the table to get the results when required, this variable used as an expression in a nested select maybe, ie a very basic example is included below: --..Perform the nested select ... Select ID from TableOne where ID in (Select ID from TableTwo) --... so maybe we could perform this with something like ... Declare @Results [SomeVariantDataTypeMaybe?] Select @Results=ID from TableTwo Select ID from TableOne where ID in (@Results) Thanks in advance! S. There is a local variable type of table which acts (almost) exactly like a
table. (There are some exceptions, see BOL). So you can say Declare @Results Table (ID int) Insert Into @Results (ID) Select ID From TableTwo Select ID From TableOne Where ID In (Select ID From @Results) Tom Show quote "StephenMcC" <Stephen***@discussions.microsoft.com> wrote in message news:3C9F1B9C-C624-4ABC-995D-4E586268990E@microsoft.com... > Do we know, is it possible to copy the results passed from a select into a > variable (I think similar in the way one can pass a resultset back from a > UFN), so one doesn't have to keep on querying the table to get the results > when required, this variable used as an expression in a nested select > maybe, > ie a very basic example is included below: > > --..Perform the nested select ... > Select ID from TableOne > where ID in (Select ID from TableTwo) > > --... so maybe we could perform this with something like ... > > Declare @Results [SomeVariantDataTypeMaybe?] > Select @Results=ID from TableTwo > > Select ID from TableOne > where ID in (@Results) > > Thanks in advance! > > S. "Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message If only this were truely so.news:X9CdnYmFAq5zCXnZnZ2dnUVZ_v-dnZ2d@comcast.com... > There is a local variable type of table which acts (almost) exactly like a > table. Don't mean to be picky but there is no such thing as a 'type' of table in the sense of a type of integer which has well defined properties. If sql server had a table type we would be speaking a different language:) http://racster.blogspot.com/ A person wears a shield of decency but what he believes you can tear to shreds. Then I guess we speak different languages :-)
I, however, usually use the Microsoft SQL Server dialect which calls it a data type, lookup table variables in BOL or see http://msdn2.microsoft.com/en-us/library/ms175010.aspx Tom Show quote "Steve Dassin" <steve@nospamrac4sql.net> wrote in message news:eLJojejwGHA.4880@TK2MSFTNGP04.phx.gbl... > "Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message > news:X9CdnYmFAq5zCXnZnZ2dnUVZ_v-dnZ2d@comcast.com... >> There is a local variable type of table which acts (almost) exactly like >> a table. > > If only this were truely so. > Don't mean to be picky but there is no such thing as a 'type' of table > in the sense of a type of integer which has well defined properties. > If sql server had a table type we would be speaking a different language:) > > http://racster.blogspot.com/ > > A person wears a shield of decency but what he believes you can tear to > shreds. > > Hi guys,
Thanks for ur responses, so the example is kinda like a tempoary table but we don't have to explicitly declare it first. Although this is a mute point (or as Joey would say the point is moo), as I'm using SyBase on this project (which is badically SQL Server7 I reckon) and it doesn't suppoprt the Table data type in this manner, damn! Man I miss SQL Server. Stephen. Show quote "Tom Cooper" wrote: > Then I guess we speak different languages :-) > > I, however, usually use the Microsoft SQL Server dialect which calls it a > data type, lookup table variables in BOL or see > http://msdn2.microsoft.com/en-us/library/ms175010.aspx > > Tom > > "Steve Dassin" <steve@nospamrac4sql.net> wrote in message > news:eLJojejwGHA.4880@TK2MSFTNGP04.phx.gbl... > > "Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message > > news:X9CdnYmFAq5zCXnZnZ2dnUVZ_v-dnZ2d@comcast.com... > >> There is a local variable type of table which acts (almost) exactly like > >> a table. > > > > If only this were truely so. > > Don't mean to be picky but there is no such thing as a 'type' of table > > in the sense of a type of integer which has well defined properties. > > If sql server had a table type we would be speaking a different language:) > > > > http://racster.blogspot.com/ > > > > A person wears a shield of decency but what he believes you can tear to > > shreds. > > > > > > > > Thanks for ur responses, so the example is kinda like a tempoary table but Perhaps you would get better responses by posting in a sybase newsgroup. > we don't have to explicitly declare it first. Although this is a mute > point > (or as Joey would say the point is moo), as I'm using SyBase on this > project > (which is badically SQL Server7 I reckon) and it doesn't suppoprt the > Table > data type in this manner, damn! Man I miss SQL Server. > > Stephen. Sybase has two different dbms products, both of which have functionality that differs substantially from sql server (not that version 7 functionality was shared by Sybase and MS). Note - the point is "moot". Ah, I see, well maybe that kind of answers my next question which was: 'Can I
get Query Analyzer to work with SyBase', as I believe SQL Server and SyBase were basically the same RDBMS, at least back in the early days, so it is poss to get QA to work with the basic SyBase functionality? Note - Mute or Moot, it's open to debate really, I like to use Mute as I think it makes more sense, which is the reason why it's often used over Moot. Many regard 'Mute' as being wrong, but that’s their business! Show quote "Scott Morris" wrote: > > Thanks for ur responses, so the example is kinda like a tempoary table but > > we don't have to explicitly declare it first. Although this is a mute > > point > > (or as Joey would say the point is moo), as I'm using SyBase on this > > project > > (which is badically SQL Server7 I reckon) and it doesn't suppoprt the > > Table > > data type in this manner, damn! Man I miss SQL Server. > > > > Stephen. > > Perhaps you would get better responses by posting in a sybase newsgroup. > Sybase has two different dbms products, both of which have functionality > that differs substantially from sql server (not that version 7 functionality > was shared by Sybase and MS). > > Note - the point is "moot". > > > "StephenMcC" <Stephen***@discussions.microsoft.com> wrote in message I doubt that QA is a tool designed to be DBMS independent. Feel free to try news:64055039-D85A-47BD-BD91-07FCBA785004@microsoft.com... > Ah, I see, well maybe that kind of answers my next question which was: > 'Can I > get Query Analyzer to work with SyBase', as I believe SQL Server and > SyBase > were basically the same RDBMS, at least back in the early days, so it is > poss > to get QA to work with the basic SyBase functionality? it. > Note - Mute or Moot, it's open to debate really, I like to use Mute as I Sorry, but the use of "mute" is wrong. Frequent misuse does not make it any > think it makes more sense, which is the reason why it's often used over > Moot. > Many regard 'Mute' as being wrong, but that's their business! more correct; though it may be somewhat understood, especially in verbal communication. If I say "intents and purposes", you may hear "intensive purposes" and use it in your own conversations. Indeed, perhaps "intensive purposes" has meaning for you (and those with whom you are speaking). Inspite of your intention, others are likely to hear and understand the original wording. Once you write it, the error -- in standard usage -- becomes revealed. Confusing words with similar sounds - a topic of many English classes. http://www.worldwidewords.org/qa/qa-moo1.htm http://eggcorns.lascribe.net/  |
|||||||||||||||||||||||
Other interesting topics