temp table problem?

[Savas Ates]

i have a stored below

it uses create temp table and drop temp table... when more than one user
request the same page it probably returns error.. how can i solve this
problem

************************************************** **************************
*****************************************
CREATE PROCEDURE st_seconddegree
@fromwhom numeric(18), @towhom numeric(18) AS
SET NOCOUNT ON
if not exists (select 1 from crosstable where (fromwhom=@fromwhom and
towhom=@towhom ) or (fromwhom=@towhom and towhom=@fromwhom))
Begin
create table #pele1
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele1 SELECT
xuserid = CASE WHEN fromwhom <> @fromwhom then @fromwhom ELSE fromwhom
END,
xarkid = CASE WHEN fromwhom = @fromwhom then towhom ELSE fromwhom END
FROM crosstable
where fromwhom=@fromwhom or towhom=@fromwhom

create table #pele2
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele2
SELECT
xuserid = CASE WHEN fromwhom <> @towhom then @towhom ELSE fromwhom END,
xarkid = CASE WHEN fromwhom = @towhom then towhom ELSE fromwhom END

FROM crosstable
where fromwhom=@towhom or towhom=@towhom

select #pele1.xarkid as xarkid from #pele1 INNER JOIN #pele2 ON
#pele1.xarkid=#pele2.xarkid;
drop table #pele1
drop table #pele2
End
GO
************************************************** **************************
**********************************************

[Keith Kratochvil]

"when more than one user request the same page it probably returns error"

Are you guessing that the stored procedure will error when multiple people
(connections/users) call it at the same time? Temp tables are only visible
to the connection that created them, therefore there will not be a problem
with multiple users calling this stored procedure at the same time.

--
Keith
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:Of*************@TK2MSFTNGP11.phx.gbl...

i have a stored below

it uses create temp table and drop temp table... when more than one user
request the same page it probably returns error.. how can i solve this
problem

************************************************** ************************** *****************************************
CREATE PROCEDURE st_seconddegree
@fromwhom numeric(18), @towhom numeric(18) AS
SET NOCOUNT ON
if not exists (select 1 from crosstable where (fromwhom=@fromwhom and
towhom=@towhom ) or (fromwhom=@towhom and towhom=@fromwhom))
Begin
create table #pele1
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele1 SELECT
xuserid = CASE WHEN fromwhom <> @fromwhom then @fromwhom ELSE fromwhom
END,
xarkid = CASE WHEN fromwhom = @fromwhom then towhom ELSE fromwhom END
FROM crosstable
where fromwhom=@fromwhom or towhom=@fromwhom

create table #pele2
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele2
SELECT
xuserid = CASE WHEN fromwhom <> @towhom then @towhom ELSE fromwhom END, xarkid = CASE WHEN fromwhom = @towhom then towhom ELSE fromwhom END

FROM crosstable
where fromwhom=@towhom or towhom=@towhom

select #pele1.xarkid as xarkid from #pele1 INNER JOIN #pele2 ON
#pele1.xarkid=#pele2.xarkid;
drop table #pele1
drop table #pele2
End
GO
************************************************** ************************** **********************************************

[Aaron [SQL Server MVP]]

> it uses create temp table and drop temp table... when more than one user

request the same page it probably returns error.. how can i solve this
problem

This isn't a problem. Have you actually observed errors? Have you tried
producing an error? Perhaps you should read up on temp tables...

--
http://www.aspfaq.com/
(Reverse address to reply.)