Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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
    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] ' +
    ' (@@tablename nvarchar(50), @@CampoFolio nvarchar(50)' +
    ' ,@@iFolio nvarchar(20) ,@update nvarchar(100))' +
    'AS ' +
    'SET @UpDate = ''UPDATE '' + @@tablename + ' +
    ' ''SET [Borrado] = ''''True'''' '' + ' +
    ' ''WHERE '' + @@CampoFolio + ''='' + @@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.