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
    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.