Using the sp_executesql in a stored procedure

mgerbs

I am currently trying to set up a stored procedure to create a temporary
table, based on different conditions, and I am using the 'sp_executesql'
statement to execute this statement. What I need to do is then use this
table that I just created in the creation of a second statement. This second statement is a select statement that includes the use of the temporary table that was just created. This statement will also be executed using the sp_executesql stored procedure. Once the first sp_executesql statement is executed, the temporary table is not recognized. I believe this is because the sp_executesql is its own session. Is there any way to accomplish what I am looking to do?

Very Frustrated.

Shanoo

What I can understand from your description is that you are using something like following:

Create Procedure up_tmpTable
As
Begin
Exec sp_executesql N'Create Table #tmp (ID int, Name varchar(10))'
Exec sp_executesql N'Select * From #tmp'
End

Try this:
Create Procedure up_tmpTable
As
Begin
Exec sp_executesql N'Create Table ##tmp (ID int, Name varchar(10))'
Exec sp_executesql N'Select * From ##tmp'
End

Note: Notice the additional # in the table name which creates a global temporary table.

aroutis

: I am currently trying to set up a stored procedure to create a temporary
: table, based on different conditions, and I am using the 'sp_executesql'
: statement to execute this statement. What I need to do is then use this
: table that I just created in the creation of a second statement. This second statement is a select statement that includes the use of the temporary table that was just created. This statement will also be executed using the sp_executesql stored procedure. Once the first sp_executesql statement is executed, the temporary table is not recognized. I believe this is because the sp_executesql is its own session. Is there any way to accomplish what I am looking to do?
:
: Very Frustrated.
:
:
There are many approaches to this... you can create a table with a fixed name and different columns based on the conditions, you can create a table with different table name and columns altogether, I am not sure what you want to do really.

However everything you want to do you CAN do with the use of Dynamic SQL and sp_executeSQL. If you need help lemme know and I can produce you with examples.

Also think of using table memory variables, that tho depends on what you need to do, and for that I am not sure.

ckeloumn

hi i have a similar problem, i'm working with a situation in wich every client has it's own data base, and i need to create some procedures in each data base created in the client register module i have a stored procedure wich has a statement for accomplish this, the statement create a procedure for deletion (in this case) but is creating the procedure in my principal data abse not in the client data base this is the code

[code]CREATE PROCEDURE [dbo].[sp_CREAR_BorradoLogico]
@sBDName nvarchar(20))
AS
DECLARE @sSQL nvarchar(500)
SET @sSQL =
'CREATE PROCEDURE ' +
'[sp_BorradoLogico] ' +
@CampoFolio nvarchar(50)' +
@iFolio nvarchar(20) ,@update nvarchar(100))' +
'AS ' +
'SET @tablename + ' +
' ''SET [Borrado] = ''''True'''' '' + ' +
@iFolio ' +
'EXEC sp_executesql @update '
EXEC sp_executesql @sSQL
go[/code]

i need the procedure [sp_BorradoLogico] to be created in the client data base passed by parameter, but if include the USE statement in te begining of the SET caluse it's trow an error
can u help me to resolve this?