Dynamic Query

• meet.binto

  SSC Rookie

  Points: 32

  More actions

  April 21, 2010 at 10:42 pm

  #234136

  Here is my code

  DECLARE @QtyPerUnitMeasure DECIMAL(38,20)

  DECLARE @QryQtyPerUntMsr NVARCHAR(2000)

  SET @QryQtyPerUntMsr='SELECT @QtyPerUnitMeasureOUT=[Qty_ per Unit of Measure] FROM

  ['+@CompanyName+'$Item Unit of Measure] AS ItmUnitMeas

  WHERE ItmUnitMeas.[Item No_]='''+@ItemNo+''' AND ItmUnitMeas.[Code]='''+@UnitMeasureCd+''''

  EXEC SP_EXECUTESQL @QryQtyPerUntMsr, N'@QtyPerUnitMeasureOUT DECIMAL(38, 20) OUTPUT',

  @QtyPerUnitMeasureOUT = @QtyPerUnitMeasure OUTPUT

  SELECT @QtyPerUnitMeasure

  I am getting result as 'Qty_ per Unit of Measure'.I want the result in a variable. '@QtyPerUnitMeasure' returning NULL.Please help me

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  April 21, 2010 at 11:15 pm

  #1155848

  One of the local variable is initiated to NUL, buddy... Check up all of your local variables for null and then include them in your dynamice sql..

• sunitkrishna

  SSCrazy

  Points: 2233

  More actions

  April 21, 2010 at 11:49 pm

  #1155860

  Instead of your statement:

  @QtyPerUnitMeasureOUT = @QtyPerUnitMeasure OUTPUT

  SELECT @QtyPerUnitMeasure

  try this:

  SELECT @QtyPerUnitMeasure=@QtyPerUnitMeasureOUT

  ---
  Thinking is the hardest work there is, which is the probable reason so few engage in it.

  Sunitha
  😎