I'm looking at some T-SQL code (in a SQL Server 2019 environment) where a parameter in a stored procedure is an "overloaded" VARCHAR (15) parameter, and later is meant to be converted to an INT. I pondered how this could go wrong -- it may not actually be a numeric string, or it might represent a number too big to fit in an INT variable. I want something efficient, and I began pondering using a TRY_CAST instead of just CAST, or using a TRY/CATCH block, to do the conversion on success, or allow "a way out" if not. To simplify...
-- given these...
declare @AParamter varchar (15) = '214748364700'; -- 100x bigger than max INT
declare @AVariable int;
-- ...approach 1...
set @AVariable = try_cast( @AParamter as int );
if (@AVariable is null) begin
print 'ERROR: invalid parameter';
end
-- ...approach 2...
begin try
set @AVariable = cast( @AParamter as int );
end try
begin catch
print 'ERROR: invalid parameter';
end catch
Both work, but I'm wondering which is more "efficient", i.e. requires less resources (and admittedly, either is probably somewhat minimal compared to everything else going on), or if either of these has any hidden "gotchas".
I'm using the sys.dm_exec_*_stats views to gather metrics for the stored procedure I'm testing, and I'm assuming the Total_Worker_Time and Total_Elapsed_Time metrics are the most relevant, but they can vary so much (within the context of the proc) that it's hard to see which approach may be having a bigger (or any) effect, for better or for worse. So, I'm asking this mostly out of curiosity as to which approach is a better practice, or what else might be even better yet.
I tried to search the web and StackOverflow for any info, but I couldn't seem to find any results referring to performance in comparison.
Note: my preference would be to not overload this parameter; me personal feeling is that it should be multiple parameters, each of the correct type, and only one of them not null at a time, with specific checks to assure that we have one and only one filled in, but... that's not really up to me.
TRY_CAST is far and away faster, as well as being much clearer.
The following script will show you that:
declare @v varchar(10) = 'hello';
declare @i int = 1;
declare @x int;
declare @dt datetime2 = sysdatetime();
while @i < 10000
begin
set @x = try_cast(@v as int);
set @i += 1;
end;
select datediff(mcs, @dt, sysdatetime());
set @i = 1;
set @dt = sysdatetime();
while @i < 10000
begin
begin try
set @x = cast(@v as int);
end try
begin catch
end catch;
set @i += 1;
end;
select datediff(mcs, @dt, sysdatetime());
10k loops takes about 1 second if you are catching, whereas TRY_CAST takes around 30ms.
The reason is probably simple: BEGIN CATCH involves a lot of gubbins with exception handling, such as walking the stack and unwinding. Whereas TRY_CAST can just return a null.