Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Simulating TRY_CONVERT() in SQL Server 2008 Part 2


By:   |   Updated: 2018-04-25   |   Comments   |   Related: More > Functions - User Defined UDF

Problem

In my last tip, I talked about ways to simulate the function TRY_CONVERT() when you’re on an older version of SQL Server. In that tip I showed some scalar-valued functions, each targeting a very specific data type, that could be used when working against a set. But I thought I’d also share a way to dynamically handle conversions to a wide variety of types – the catch being that it can’t be done inside a function (“dynamically” is the hint there).

Solution

First, let’s assume you have some entity-attribute-value (EAV) setup, like a configuration table.

CREATE TABLE dbo.Configurations
(
  Setting sysname PRIMARY KEY,
  DataType varchar(32) NOT NULL,
  Value nvarchar(4000) NOT NULL
);
INSERT dbo.Configurations(Setting,DataType,Value) VALUES
(N'ServerName',      'sysname', N'HONEYBADGER'),
(N'AppTimeout',      'int',     900),
(N'FounderBirthday', 'date',    '19740201');		

Now, let’s assume you have an application that absolutely needs a strongly-typed column in the output, and expects to call a stored procedure and get the single configuration value – with the right type – in the only output column. Without that requirement, you could just do this:

CREATE PROCEDURE dbo.GetConfigurationValue
  @Setting sysname
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Value 
    FROM dbo.Configurations
    WHERE Setting = @Setting;
END
GO	

But, as developers have told me, the receiving code can’t just swap out that data type on the fly. There was definitely some talk about reflection and strong typing, and my eyes glazed over. The problem they needed to solve is that Value needed to be dynamically typed to the right data type, so when they were asking for AppTimeout, they would get an int, and when they were asking for FounderBirthday, they would get a date.

There are a couple of ways to accomplish this without TRY_CONVERT() and, in fact, even with TRY_CONVERT(), it would have to be dynamic, since the first argument can’t be a variable or parameter.

Simulating TRY_CONVERT() - Dynamic SQL Option #1

To facilitate roughly what TRY_CONVERT() does, the point is to generate a column with the right data type, but also to avoid raising an exception if the value stored doesn’t match the defined data type. You can accomplish this by using dynamic SQL and wrapping an explicit attempt to convert a variable in TRY / CATCH (introduced in SQL Server 2008).

CREATE PROCEDURE dbo.GetSetting_Option1
  @Setting sysname
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @DataType varchar(32), 
          @Value    nvarchar(4000), 
          @sql      nvarchar(max);

  SELECT @DataType = DataType, @Value = Value
    FROM dbo.Configurations
    WHERE Setting = @Setting;

  -- try assigning to a variable of the right data type
  -- if it works, select it
  SET @sql = N'DECLARE @t ' + @DataType + N' = @v;
      SELECT Value = @t;';

  BEGIN TRY
    EXEC sys.sp_executesql @sql, N'@v nvarchar(4000)', @Value;
  END TRY
  BEGIN CATCH
    -- if it doesn't work, strongly type NULL
    SET @sql = N'SELECT Value = CONVERT(' + @DataType + N', NULL);';
    EXEC sys.sp_executesql @sql;
  END CATCH
END
GO	

Simulating TRY_CONVERT() - Dynamic SQL Option #2 – SQL Server 2012+

This one assumes you are on a more modern version of SQL Server, and can use TRY_CONVERT(). It simply builds the dynamic SQL statement to select from the table by first selecting from the table:

CREATE PROCEDURE dbo.GetSetting_Option2
  @Setting sysname
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max);
  
  SELECT @sql = N'SELECT Value = 
      TRY_CONVERT(' + DataType + N', Value)
    FROM dbo.Configurations
    WHERE Setting = @Setting;'
  FROM dbo.Configurations
  WHERE Setting = @Setting;
  EXEC sys.sp_executesql @sql, N'@Setting sysname', @Setting;
END
GO	

That is just to demonstrate that even with newer functionality there still needs to be some dynamic elements built in in order to meet the application requirements of strongly-typed output columns.

Testing

You can test how either solution works by inserting a “broken” value in the Configurations table:

INSERT dbo.Configurations(Setting, DataType, Value)
  VALUES(N'Broken', 'int', N'foo');			

And then just try:

EXEC dbo.GetSetting_Option1 @Setting = N'Broken';
EXEC dbo.GetSetting_Option1 @Setting = N'FounderBirthday';
EXEC dbo.GetSetting_Option2 @Setting = N'Broken';
EXEC dbo.GetSetting_Option2 @Setting = N'FounderBirthday';			
Next Steps

Read on for related tips and other resources:



Last Updated: 2018-04-25


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools