|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SET OF INTI need to get a set of int values from some query and use this set later in
the same SP. The query is looking like this: (SELECT SomeID FROM tblTable WHERE SOMECONDITION ORDER BY SomeID) How can I store the set of these integer values into a temporary variable and what type should I use to insert this variable into next query like: IF SELECT ... IN SETOFINT BEGIN END Just D. Is there any reason you need to separate the two statements? That is,
can you just say: select blah from mytable where mycol in (select SomeID FROM tblTable WHERE ...) -- or alternately select blah from mytable m inner join (select distinct SomeID from tblTable where ...) as d on d.SomeID = m.mycol where ... If you can't do that and you really want to store the results away temporarily you can just do this: select distinct SomeID into #tmp from tblTable where ... -- Do other stuff here in your stored proc -- Now use that temp list of IDs: select OtherStuff from SomeOtherTable where SomeID in (select SomeID from #tmp) -- Or better yet: select OtherStuff from SomeOtherTable o inner join #tmp t on t.SomeID = o.SomeID where .... HTH Show quote >I need to get a set of int values from some query and use this set later in >the same SP. The query is looking like this: > > (SELECT SomeID FROM tblTable WHERE SOMECONDITION ORDER BY SomeID) > >How can I store the set of these integer values into a temporary variable >and what type should I use to insert this variable into next query like: > >IF SELECT ... IN SETOFINT BEGIN > >END > >Just D. > > > > Hi Mike,
You know the main reason why I wanted to separate this calculation was to run it only once. Actually ir's not a very simple SELECT, but a very complicated query with many joins, conditions, etc. The result should be returned as a set of some IDs that should be used in next two complicated queries in both sides IN and NOT IN. Don't ask me why it should be done this way, the schema is too complicated and I spent a very long time thinking how to create this query to make it fast and as short as possible, it's about 5.5 kbytes now, pretty well optimized. The rest is done in C# when I finally get this DataSet, but my idea was to use maximum of T-SQL to get the final table as it supposed to be without any additional C# code. To save them to a temporary table maybe is not so bad idea but I suspect not ideal either. Some temporary variable would be much more appropriate. I understand that 5.5 kbytes sounds funny, I was writing much longer scripts in T-SQL, over Mbyte size, but the idea of this SP is to return a result from a few huge tables using joins, comparisons, a wide set of conditions, etc., and it should work as fast as possible. Double running is not good to retrieve the same ID set. Just D. "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message Is there any reason you need to separate the two statements? That is, can news:eq8s7ShqFHA.3600@tk2msftngp13.phx.gbl... you just say: select blah from mytable where mycol in (select SomeID FROM tblTable WHERE ...) -- or alternately select blah from mytable m inner join (select distinct SomeID from tblTable where ...) as d on d.SomeID = m.mycol where ... If you can't do that and you really want to store the results away temporarily you can just do this: select distinct SomeID into #tmp from tblTable where ... -- Do other stuff here in your stored proc -- Now use that temp list of IDs: select OtherStuff from SomeOtherTable where SomeID in (select SomeID from #tmp) -- Or better yet: select OtherStuff from SomeOtherTable o inner join #tmp t on t.SomeID = o.SomeID where .... HTH -- mike hodgson blog: http://sqlnerd.blogspot.com Just D. wrote: I need to get a set of int values from some query and use this set later in the same SP. The query is looking like this: (SELECT SomeID FROM tblTable WHERE SOMECONDITION ORDER BY SomeID) How can I store the set of these integer values into a temporary variable and what type should I use to insert this variable into next query like: IF SELECT ... IN SETOFINT BEGIN END Just D. Yeah, OK. In that case selecting those IDs into a temp table is
probably the best option. There's very little real difference between a temp table and a table variable (table variables are mostly for returning a result set from a UDF as opposed to a single scalar value) & I tend to prefer temp tables to table variables mostly since I was "brought up" on them. I understand your logic behind wanting to do it this way and, to be honest, I do the same thing myself occasionally (using a temp table to store some results temporarily so they can then be joined to multiple other statements without having to go through that expensive execution plan again). Another option you may want to have a bit of a think about would be creating an indexed view for that data you plan to join into multiple statements (the stuff you want to stash in a temp table). That would simplify your proc by taking a chunk of the code out (and "saving" it in the view), allow fast retrieval of that data due to it being materialised by the clustered index on the view and avoid temp tables & local variables in your proc to deal with that data (it just becomes a simple "table" to join in to the statements in your proc for all intents & purposes). I don't know if this is feasible in your circumstances but it's worth considering. Show quote >Hi Mike, > >You know the main reason why I wanted to separate this calculation was to >run it only once. Actually ir's not a very simple SELECT, but a very >complicated query with many joins, conditions, etc. The result should be >returned as a set of some IDs that should be used in next two complicated >queries in both sides IN and NOT IN. Don't ask me why it should be done this >way, the schema is too complicated and I spent a very long time thinking how >to create this query to make it fast and as short as possible, it's about >5.5 kbytes now, pretty well optimized. The rest is done in C# when I finally >get this DataSet, but my idea was to use maximum of T-SQL to get the final >table as it supposed to be without any additional C# code. To save them to a >temporary table maybe is not so bad idea but I suspect not ideal either. >Some temporary variable would be much more appropriate. > >I understand that 5.5 kbytes sounds funny, I was writing much longer scripts >in T-SQL, over Mbyte size, but the idea of this SP is to return a result >from a few huge tables using joins, comparisons, a wide set of conditions, >etc., and it should work as fast as possible. Double running is not good to >retrieve the same ID set. > >Just D. > >"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message >news:eq8s7ShqFHA.3600@tk2msftngp13.phx.gbl... >Is there any reason you need to separate the two statements? That is, can >you just say: > >select blah from mytable >where mycol in (select SomeID FROM tblTable WHERE ...) > >-- or alternately > >select blah from mytable m > inner join (select distinct SomeID from tblTable where ...) as d on >d.SomeID = m.mycol >where ... > > >If you can't do that and you really want to store the results away >temporarily you can just do this: > >select distinct SomeID into #tmp from tblTable where ... > >-- Do other stuff here in your stored proc > >-- Now use that temp list of IDs: >select OtherStuff from SomeOtherTable >where SomeID in (select SomeID from #tmp) > >-- Or better yet: >select OtherStuff from SomeOtherTable o > inner join #tmp t on t.SomeID = o.SomeID >where .... > > >HTH > >-- >mike hodgson >blog: http://sqlnerd.blogspot.com > > >Just D. wrote: >I need to get a set of int values from some query and use this set later in >the same SP. The query is looking like this: > > (SELECT SomeID FROM tblTable WHERE SOMECONDITION ORDER BY SomeID) > >How can I store the set of these integer values into a temporary variable >and what type should I use to insert this variable into next query like: > >IF SELECT ... IN SETOFINT BEGIN > >END > >Just D. > > > > > > > I prefer table variables for this kind of thing over temp tables, because
they only exist within the context in which they are declared, and any modifications to them survive a rollback, whereas modifications to temp tables are backed out. You need to be careful, however. In a concurrent environment, it is possible for one or more of the source rows of the complicated query to change after they're read out but before the end of the procedure, and if the results are then used in a join or an update later on, the results returned are suspect, or even worse, the update just made the database inconsistent. "Just D." <no@spam.please> wrote in message news:NtAPe.47076$Ji4.22905@fed1read03...Show quote > Hi Mike, > > You know the main reason why I wanted to separate this calculation was to > run it only once. Actually ir's not a very simple SELECT, but a very > complicated query with many joins, conditions, etc. The result should be > returned as a set of some IDs that should be used in next two complicated > queries in both sides IN and NOT IN. Don't ask me why it should be done this > way, the schema is too complicated and I spent a very long time thinking how > to create this query to make it fast and as short as possible, it's about > 5.5 kbytes now, pretty well optimized. The rest is done in C# when I finally > get this DataSet, but my idea was to use maximum of T-SQL to get the final > table as it supposed to be without any additional C# code. To save them to a > temporary table maybe is not so bad idea but I suspect not ideal either. > Some temporary variable would be much more appropriate. > > I understand that 5.5 kbytes sounds funny, I was writing much longer scripts > in T-SQL, over Mbyte size, but the idea of this SP is to return a result > from a few huge tables using joins, comparisons, a wide set of conditions, > etc., and it should work as fast as possible. Double running is not good to > retrieve the same ID set. > > Just D. > > "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message > news:eq8s7ShqFHA.3600@tk2msftngp13.phx.gbl... > Is there any reason you need to separate the two statements? That is, can > you just say: > > select blah from mytable > where mycol in (select SomeID FROM tblTable WHERE ...) > > -- or alternately > > select blah from mytable m > inner join (select distinct SomeID from tblTable where ...) as d on > d.SomeID = m.mycol > where ... > > > If you can't do that and you really want to store the results away > temporarily you can just do this: > > select distinct SomeID into #tmp from tblTable where ... > > -- Do other stuff here in your stored proc > > -- Now use that temp list of IDs: > select OtherStuff from SomeOtherTable > where SomeID in (select SomeID from #tmp) > > -- Or better yet: > select OtherStuff from SomeOtherTable o > inner join #tmp t on t.SomeID = o.SomeID > where .... > > > HTH > > -- > mike hodgson > blog: http://sqlnerd.blogspot.com > > > Just D. wrote: > I need to get a set of int values from some query and use this set later in > the same SP. The query is looking like this: > > (SELECT SomeID FROM tblTable WHERE SOMECONDITION ORDER BY SomeID) > > How can I store the set of these integer values into a temporary variable > and what type should I use to insert this variable into next query like: > > IF SELECT ... IN SETOFINT BEGIN > > END > > Just D. > > > > >  |
|||||||||||||||||||||||
Other interesting topics