CODEWITHSUNDEEP
sql-server-2008t-sqldynamic-sql
GoldBishop
GoldBishop

Reputation: 2861

Convert NULL Text to Null Value

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.

Upvotes: 0

Views: 246

Answers (1)

bastos.sergio
bastos.sergio

Reputation: 6764

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

Upvotes: 2

Related Questions