EXECUTE sp_executesql

Question

EXECUTE sp_executesql

elkinfortiz

Mr or Mrs. 500

Points: 533
More actions
December 4, 2019 at 5:22 pm

#3704413

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

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

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

ScottPletcher SSC Guru Points: 101097 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

elkinfortiz Mr or Mrs. 500 Points: 533 More actions · Answer 2

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: 99130 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

elkinfortiz Mr or Mrs. 500 Points: 533 More actions · Answer 5

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 Mr or Mrs. 500 Points: 533 More actions · Answer 6

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.