Using the sp_executesql in a stored procedure - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Using the sp_executesql in a stored procedure

mgerbsmgerbs Posts: 1Member
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.

Comments

  • ShanooShanoo Posts: 32Member
    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.
  • aroutisaroutis Posts: 1Member
    : 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.
  • ckeloumnckeloumn Posts: 1Member
    @sBDName nvarchar(20))
    AS
    DECLARE @sSQL nvarchar(500)
    SET @iFolio nvarchar(20) ,@update nvarchar(100))' +
    'AS ' +
    'SET @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?
Sign In or Register to comment.