CODEWITHSUNDEEP
sql-servert-sql
Prescott Chartier
Prescott Chartier

Reputation: 1653

Dynamic SQL (T-Sql) returns varchar instead of float

Apparently my T-Sql skills are a little rusty. I have the following table type defined:

CREATE TYPE QualityControlData 
   AS TABLE
      ( SU float            -- Uniformity
      , ND float            -- Good
      , DIS float           -- Dissimilar
      , DBL float           -- Doubles
      , CS float            -- CS64
      , FM float            -- EmbeddedShell + Organic + Nonorganic
      , PHD float           -- PinHoles
      , SNB float           -- SplitBroken
      , OD float            -- OtherDefects
      , SHRV float          -- Shrivel
      , Gum float           -- GUM
      , BRNS float          -- BrownSpot
      , SD float            -- SeriousDamage
      , INS float           -- InsectInjury
      , MLD float           -- DMR
      , FR float            -- Frass
      , TK char(255)        -- MD4
      , EMY float);         -- Good + Dissimilar + doubles + chip + scratch + cs64 + split + broken + otherdefects
GO

I declare a variable of the table type:

Declare @TestData QualityControlData

I create and execute the following query:

Declare @GetQCValueCommand nvarchar(max)
set @GetQCValueCommand = 'select ' + @QCTestID + ' from @TestData'
EXEC @QCValue = sp_executesql @GetQCValueCommand, N'@TestData QualityControlData readonly',@TestData

QCTestID is equal to 'SU', so the query ends up looking like:

select SU from @TestData

The value of the SU column in the data table is float 0.2324422, the query returns a varchar and the QCValue is zero ('0'). I figured this out when the procedure crashed when it couldn't convert the returned value to float. What am I missing? Is this normal for T-Sql dynamic Sql and do I need to convert the data when selecting it or am I not understanding something?

Upvotes: 0

Views: 282

Answers (1)

Thom A
Thom A

Reputation: 95588

0 is correct, you're setting the value of @QCValue to the RETURN value of sp_executesql and 0 means success. From RETURN (Transact-SQL):

Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure.

If you want to output a scalar value you want to do the following:

DECLARE @QCTestID sysname = N'SU',
        @QCValue float;

DECLARE @GetQCValueCommand nvarchar(MAX);
SET @GetQCValueCommand = N'SELECT @QCValue = ' + QUOTENAME(@QCTestID) + N' FROM @TestData;'; --Safely quoted value
EXEC sp_executesql @GetQCValueCommand,
                   N'@TestData QualityControlData readonly, @QCValue float OUTPUT', --Added output parameter
                   @TestData,
                   @QCValue OUTPUT;--Added output parameter

SELECT @QCValue;

Edit: also, the value previously returned wasn't varchar, it was an int.

Upvotes: 4

Related Questions