EXECUTE sp_executesql

  • elkinfortiz

    SSC-Addicted

    Points: 473

    Cordial Saludo. tengo el siguiente script

    DECLARE @SqlString NVARCHAR(500)

    DECLARE @ParmDefinition NVARCHAR(500)

    DECLARE @Valor_Tmp Numeric(12,2)

    SET @SqlString=LTRIM(RTRIM(@ValorFrm))

    SET @ParmDefinition = N'@Valor_OUT Numeric(12,2) OUTPUT'

    EXECUTE sp_executesql @SqlString,@ParmDefinition,@Valor_OUT=@Valor_Tmp OUTPUT

    SET @Valor=@Valor_Tmp

    la variable @ValorFrm='SET @Valor_OUT=983,5-2(15.3)-1' Esta variable es una construccion similar a a+2(b)-1 construida por programa. El problema es que en el SSMS funciona y ejecuta bien es execute, pero cuando lo coloco en un procedimiento que es llamado por otro, el sistema se queda indefinidamente ejecutando la consulta. Ej, el proc A llama al proc B y en B se ejecuta este bloque. Debo de tener alguna consideracion adicional para que esto me funcione a ese nivel? Gracias por su ayuda

  • ScottPletcher

    SSC Guru

    Points: 98398

    A basic numeric calculation should not take long to execute.  Look at the @ParmDefinition and make sure that it is not a calling stored procedure or doing something else that could take a very long time to run or get into a loop while running.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • elkinfortiz

    SSC-Addicted

    Points: 473

    Gracias por responder. El parametro  @ ParmDefinition  Es una variable Unicode exigida por el procedimiento sp_executesql. Desde tu perspectiva, estoy seguro que no es una invocacion a otro store procedure, funcion o cualquiera que inplique mas proceso.

  • Thom A

    SSC Guru

    Points: 98563

    elkinfortiz wrote:

    Gracias por responder. El parametro  @ ParmDefinition  Es una variable Unicode exigida por el procedimiento sp_executesql. Desde tu perspectiva, estoy seguro que no es una invocacion a otro store procedure, funcion o cualquiera que inplique mas proceso.

    You'll be far better off posting your posts in English, as many of us don't understand what you're saying without Google Translate; and bot made translations can give undesired results.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • ScottPletcher

    SSC Guru

    Points: 98398

    Ok, interesting.  Can you give an example of code that is indefinitely executing?  Are you sure it is this sp_executesql code and not some other code in the proc?

    [Yes, I used Google translate to read the posts, I do not know Spanish.]

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • elkinfortiz

    SSC-Addicted

    Points: 473

    Ok.

    This is the original formula: a.arpAncho-(2*L.apzCalibre)-1

    This is the interpreted formula: 983- (2 * 15) -1

    There is a process that converts the field to Value. This works well. Receive the original formula and convert it into the interpreted formula.

    I want to get the total of the operation: 983- (2 * 15) -1 = 952 all in millimeters.

    Process A calls Process B. In process B the sp_executesql is executed, with which I want to find the total of the operation and return it to process A so that it executes other tasks.

    In the SSMS it works well. But within process B it remains indefinitely looking for the total operation, which apparently is very simple.

    This is the block of instructions that I am executing:

    DECLARE @SqlString NVARCHAR (500)

    DECLARE @ParmDefinition NVARCHAR (500)

    DECLARE @Valor_Tmp Numeric (12,2)

    SET @ SqlString = LTRIM (RTRIM (@ValorFrm))

    SET @ParmDefinition = N '@Valor_OUT Numeric (12,2) OUTPUT'

    EXECUTE sp_executesql @ SqlString, @ ParmDefinition, @Valor_OUT = @Valor_Tmp OUTPUT

    SET @ Value = @ Valor_Tmp

    The variable @ ValorFrm = 'SET @Valor_OUT = 983- (2 * 15) -1'

    The variable @Valor is the output varible in the Process B for Process A.

    a.arpAncho= Workpiece width

    l.apzCalibre=piece gauge.

    What am I skipping or don't know so that the instruction block works correctly in process B?

    Thanks for your help. Greetings from Medellin, Colombia.

  • elkinfortiz

    SSC-Addicted

    Points: 473

    I consider it important to provide the following detail: These processes were built with the 2008 sql server engine. Now I am reviewing the 2014 sql server engine. It is where I find the difficulty. This may or may not be important, but perhaps it can make a difference.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply