Convert NULL Text to Null Value

Question

I have a situation where I may need to pass a variable that could possibly hold a NULL value to a function, but through sp_executesql, so I will need to convert it to string value via string concatenation.

For Example:

declare @var1 varchar(10) = 'value'
declare @var2 varchar(10) = null
declare @sql nvarchar(2000)
.
.
set @sql = '
  select dbo.fn_Scalar(''' + @var1 + ''', ''' + @var2 + ''' )
'

Function Definition:

Create Function [dbo].[fn_fn_Scalar] 
(
    @var1 varchar(10) ,
    @var2 varchar(10) = null
) RETURNS float
AS BEGIN
  Declare @ret float

  Select @ret = sum(value)
  from Table
  where Field1 = @var1
    and Field2 like isnull(@var2, '%')

  return @ret
END

What would be the best approach to allow for fn_Scalar to be called via Dynamic and Static SQL statements and still allow for the second parameter to either be set to a value, NULL, or default.

bastos.sergio · Accepted Answer · 2013-06-28 15:13:16Z

2

You can pass parameters to sp_executesql function like so:

declare @var1 varchar(10) = 'value'
declare @var2 varchar(10) = null
Set @ParamDefinition = '@var1 varchar(10), @var2 varchar(10)'

Execute sp_Executesql 'select dbo.fn_Scalar(@var1,@var2)', @ParamDefinition, @var1, @var2

answered Jun 28, 2013 at 15:13

bastos.sergio

6,7644 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GoldBishop Over a year ago

Good, forgot about running a parameterized query like that. Off-topic Side note: Will executing this style of SQL help mitigate Injections?

bastos.sergio Over a year ago

It helps prevent SQL Injections, as long as it's used properly.

GoldBishop Over a year ago

as with most tools, misuse is very dangerous.

Collectives™ on Stack Overflow

Convert NULL Text to Null Value

For Example:

Function Definition:

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

For Example:

Function Definition:

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related