|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SP - drop table problemIn my store procedure, i will select some data into a temp table and then
drop it . I am wonder if there are several people run the SP , will they create the same table at the same table ?? Please help ~ select billingcode ,cocode into #tmpInfo from companyinfo where rtrim(billingcode) = @billcode ...... drop table #tmpInfo #temp tables are session specific. Two different sessions can create them
at the exact same time, fill them with different data, and they won't collide. A simple experiment (e.g. two query windows in Query Analyzer) should prove this quite handily. Show quote "Agnes" <ag***@dynamictech.com.hk> wrote in message news:%23j4XM97eGHA.4304@TK2MSFTNGP05.phx.gbl... > In my store procedure, i will select some data into a temp table and then > drop it . > I am wonder if there are several people run the SP , will they create the > same table at the same table ?? > Please help ~ > > select billingcode ,cocode into #tmpInfo from companyinfo where > rtrim(billingcode) = @billcode > ..... > drop table #tmpInfo > > > > In your example you are using local temporary tables and these can be seen
only from the connection that creates them. When the connection finishes these tables are deleted. Local temporary tables names start with the sign #. There are also global temporary tables but these have names starting with ##. Ben Nevarez, MCDBA, OCP Database Administrator Show quote "Agnes" wrote: > In my store procedure, i will select some data into a temp table and then > drop it . > I am wonder if there are several people run the SP , will they create the > same table at the same table ?? > Please help ~ > > select billingcode ,cocode into #tmpInfo from companyinfo where > rtrim(billingcode) = @billcode > ...... > drop table #tmpInfo > > > > > So, Does my SP are correct ??
Thanks in advance Show quote "Ben Nevarez" <bneva***@sjm.com> ¼¶¼g©ó¶l¥ó·s»D:1676BF68-7412-497E-AA05-85BD97D81***@microsoft.com... > > In your example you are using local temporary tables and these can be seen > only from the connection that creates them. When the connection finishes > these tables are deleted. Local temporary tables names start with the sign > #. > > There are also global temporary tables but these have names starting with > ##. > > Ben Nevarez, MCDBA, OCP > Database Administrator > > > "Agnes" wrote: > >> In my store procedure, i will select some data into a temp table and then >> drop it . >> I am wonder if there are several people run the SP , will they create the >> same table at the same table ?? >> Please help ~ >> >> select billingcode ,cocode into #tmpInfo from companyinfo where >> rtrim(billingcode) = @billcode >> ...... >> drop table #tmpInfo >> >> >> >> >> Yes, if several people run the same stored procedure they will create their
own local temporary table. One session can not see other session's table. Ben Nevarez, MCDBA, OCP Database Administrator Show quote "Agnes" wrote: > So, Does my SP are correct ?? > Thanks in advance > "Ben Nevarez" <bneva***@sjm.com> ¼¶¼g©ó¶l¥ó·s»D:1676BF68-7412-497E-AA05-85BD97D81***@microsoft.com... > > > > In your example you are using local temporary tables and these can be seen > > only from the connection that creates them. When the connection finishes > > these tables are deleted. Local temporary tables names start with the sign > > #. > > > > There are also global temporary tables but these have names starting with > > ##. > > > > Ben Nevarez, MCDBA, OCP > > Database Administrator > > > > > > "Agnes" wrote: > > > >> In my store procedure, i will select some data into a temp table and then > >> drop it . > >> I am wonder if there are several people run the SP , will they create the > >> same table at the same table ?? > >> Please help ~ > >> > >> select billingcode ,cocode into #tmpInfo from companyinfo where > >> rtrim(billingcode) = @billcode > >> ...... > >> drop table #tmpInfo > >> > >> > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics