1

I am trying to handle null varible in dynamic sql query

query is as follows

DECLARE @_param_one VARCHAR(256)=NULL DECLARE @_param_two VARCHAR(256)='' DECLARE @SQLStr VARCHAR(MAX)

the below select statement is working as expected if it is not converted as a string

SELECT (SELECT CASE WHEN(COALESCE(@_param_one, '' ) = '') THEN 'WORKING' ELSE @_param_one END) AS [Param_One]

After converting to string and execute that string is not working as expected. The executed string is showing null.

SET @SQLStr='SELECT DISTINCT Column_One, Column_Two, Column_Three (SELECT CASE WHEN(COALESCE( '''+@_param_one+''', '''' ) = '''') THEN Param_One ELSE '''+@_param_one+''' END) AS [Param_One] FROM Table_One AS [![enter image description here][1]][1]Tbl WHERE Tbl.Id=2'

select @SQLStr

enter image description here

gary
  • 140
  • 4
  • 19
  • 1
    check null before dynamic sql and make them blank string or any static string. also you can check which string is finally generated before execute by using Print function. – Hasan Mahmood Apr 08 '19 at 19:40
  • Setting null to empty before executing the dynamic sql query isn't working. @hasan mahmood – gary Apr 08 '19 at 20:01
  • `IF @_param_one IS NULL SET @_param_one = '' SET @SQLStr='SELECT DISTINCT Column_One, Column_Two, Column_Three (SELECT CASE WHEN(COALESCE( '''+@_param_one+''', '''' ) = '''') THEN Param_One ELSE '''+@_param_one+''' END) AS [Param_One] FROM Table_One AS [![enter image description here][1]][1]Tbl WHERE Tbl.Id=2' print @SQLStr' now see what is wrong in this dynamic sql – Hasan Mahmood Apr 08 '19 at 20:10

2 Answers2

1

For this I would use sp_executesql instead of a basic EXEC for your Dynamic SQL. First some simplified sample data:

IF OBJECT_ID('tempdb..#table_one','U') IS NOT NULL DROP TABLE #table_one;
SELECT ID
INTO   #table_one
FROM   (VALUES (1),(2),(2),(3),(5)) AS x(ID);

The sp_executesql solution would look like this:

DECLARE @Param_One VARCHAR(512), @sql NVARCHAR(4000);

SET @sql = 
N'SELECT DISTINCT CASE WHEN COALESCE(@Param_one,'''') = ''''
                THEN ''<blank>'' ELSE @Param_one END
FROM #table_one AS t
WHERE t.id = 2;'

EXEC sp_executesql @sql, N'@Param_One VARCHAR(256)', @Param_One = @Param_One;

Note that I'm returning the text, "blank" when your @param_one is blank of null -- I didn't understand your case statement. This code, however, should get you going. Feel free to respond with followup questions.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
0

Among many things wrong with above statement (e.g. SQL injection), null is a special case within SQL. Putting NULL as the word does not mean NULL in the SQL sense. You could add a bit of code to detect when NULL is the intended parameter and change the SQL statement accordingly.

In short, simply replacing the parameters as a string will not always work.

Howard Grimberg
  • 2,128
  • 2
  • 16
  • 28
  • Not an expert with .NET but this looks like a step in the right direction https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples This is a similar problem in PHP https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Howard Grimberg Apr 08 '19 at 19:21