Simulating TRY_CONVERT() in SQL Server 2008 Part 2

By:   |   Comments   |   Related: > Functions User Defined UDF


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).


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

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

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

  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;';

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

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

  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;

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.


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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms