"Karl XII" <Karl XII@discussions.microsoft.com> wrote in message
news:8289AA06-44A7-44C8-B182-643F3DCE5D60@microsoft.com...
> Hi all!
>
> I am having problem with a stored procedure in which I have an input
> parameter that is dynamic. I want to use that parameter - in this case the
> sessionid for the current user - to execute an SQL Query and store the
result
> in a temp table named as the sessionid.
>
> For example...
>
> Select x, y, z
> INTO #@sessionID
> FROM.....
>
> It doesn´t work like the above. SQL don´t recognize @sessionID and even if
I
> try to use into @sessionID or whatever with the @seesionID containing
> "#234343" it doesn´t work. How could I succeed in doing that ? Remember
that
> I want a uniquely used temp table every time I call the stored procedure
but
> it must remain the same during the users session so sessionid would be
ideal.
>
> Thank you for all kind of help on this
>

"Karl XII" <Karl XII@discussions.microsoft.com> wrote in message
news:8289AA06-44A7-44C8-B182-643F3DCE5D60@microsoft.com...
> Hi all!
>
> I am having problem with a stored procedure in which I have an input
> parameter that is dynamic. I want to use that parameter - in this case the
> sessionid for the current user - to execute an SQL Query and store the
> result
> in a temp table named as the sessionid.
>
> For example...
>
> Select x, y, z
> INTO #@sessionID
> FROM.....
>
> It doesn´t work like the above. SQL don´t recognize @sessionID and even if
> I
> try to use into @sessionID or whatever with the @seesionID containing
> "#234343" it doesn´t work. How could I succeed in doing that ? Remember
> that
> I want a uniquely used temp table every time I call the stored procedure
> but
> it must remain the same during the users session so sessionid would be
> ideal.
>
> Thank you for all kind of help on this
>

"Wayne Snyder" wrote:

> THere are a couple of things which are hurting you...
> 1. to do what you want you'd have to use dynamic sql ie
>
> put the string together and exec it ie
>
> exec ('insert into #' + @sessionID + ' From ...')
> 2. this kind of table will automatically be kept separate for every spid no
> matter what you call it, when you create it with a single pound #, SQL
> automatically puts a uniquifier on the end of whatever table name you choose
> so no other spid can even see it but.
> 3. non-sharable temp tables created in a stored procedure automatically are
> dropped when the stored procedure exists, so
> 4. Create a sharable temp table ie exec ( 'insert into ##' + @Sessionid or
> exec ( 'insert into tempdb..' + @session id
>
> these tables ARE sharable, so you must uniqufiy them, and they will exist
> until you drop them or sql server is restarted.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Karl XII" <Karl XII@discussions.microsoft.com> wrote in message
> news:8289AA06-44A7-44C8-B182-643F3DCE5D60@microsoft.com...
> > Hi all!
> >
> > I am having problem with a stored procedure in which I have an input
> > parameter that is dynamic. I want to use that parameter - in this case the
> > sessionid for the current user - to execute an SQL Query and store the
> result
> > in a temp table named as the sessionid.
> >
> > For example...
> >
> > Select x, y, z
> > INTO #@sessionID
> > FROM.....
> >
> > It doesn´t work like the above. SQL don´t recognize @sessionID and even if
> I
> > try to use into @sessionID or whatever with the @seesionID containing
> > "#234343" it doesn´t work. How could I succeed in doing that ? Remember
> that
> > I want a uniquely used temp table every time I call the stored procedure
> but
> > it must remain the same during the users session so sessionid would be
> ideal.
> >
> > Thank you for all kind of help on this
> >
>
>