Tokens for SQL statement fill in blank instead of NULL making it crash...

Question

I have a sql statement like this:

DECLARE @MyVariable as varchar(50)
SET @MyVariable = $(TokenValue)

In this the $(TokenValue) will fill in a value from a form (ignore how it's doing it, it's not important, just that if there's a value in the field it relates to it'll get filled in there). If The field in the form was left blank however, there will be nothing put there, leaving the end result like this:

DECLARE @MyVariable as varchar(50)
SET @MyVariable =

rather than

DECLARE @MyVariable as varchar(50)
SET @MyVariable = 'FormInputValue'

Since there's nothing after the =, how can I account for this and make sure the SQL statement doesn't crash? The token never writes null to it's place, always just a blank if the field was left empty... Any ideas?

Thanks,
Matt

Answer 1

I am afraid that you will not be able to bypass this.

This is something that should be handed at the application level.

If $(TokenValue) isnot a param, and you do not include the ' in the string value you can try using

SET @MyVariable = '$(TokenValue)'

This will not work with all var types (INT and FLOAT works, but not decimal)

--WORKS
DECLARE @MyVariable as INT
SET @MyVariable = ''
SELECT @MyVariable
--WORKS
DECLARE @MyVariable as FLOAT
SET @MyVariable = ''
SELECT @MyVariable
--DOES NOT WORK
DECLARE @MyVariable as DECIMAL(18,8)
SET @MyVariable = ''
SELECT @MyVariable

Answer 2

Edited for SQL Server :) If you write your query like this:

set @MyVariable = ''$(TokenValue)''
if @MyVariable = ''''
    set @MyVariable = null
else
    set @MyVariable = substring(@MyVariable,2,len(@MyVariable)-2)

If nothing is in the place of $(TokenValue), you get:

if '''' = ''''

and @MyVariable will be set to NULL. If there is a string in the place of $(TokenValue), you get:

if '''teststring''' = ''''
     ^^^^^^^^^^^^

Because '' is the way to escape a single quote, each '' results in one quote in @MyVariable. Those two quotes are stripped away by the substring in the else clause.

Answer 3

you can try playing with default parameters e.g

create PROCEDURE dbo.test
    @v varchar(50) = ''
AS
BEGIN
    select @v
END
GO

and then

declare @a table (v varchar(50))

insert into @a
exec dbo.test -- that represents scenario with no token

insert into @a
exec dbo.test 'blah' -- that if there is a token

select * from @a

So in your case instead of

DECLARE @MyVariable as varchar(50)
SET @MyVariable = $(TokenValue)

you would end up with

DECLARE @MyVariable as varchar(50)
declare @a table (v varchar(50))
insert into @a
exec dbo.test  $(TokenValue)
select @myVariable = v from @a

But that is really hacky solution. As other suggested it should be rather filtered on the application level