CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Thangadurai.B
Thangadurai.B

Reputation: 561

How to pass null value in dynamic query

I'm trying to execute following dynamic query,I've just passed dynamical parameter to this query.

DECLARE @TABLE TABLE(ID INT,NAME VARCHAR(10))
INSERT INTO @TABLE
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,NULL

DECLARE @NAME VARCHAR(20)=NULL,
        @SQL VARCHAR(MAX)
        SET @SQL='SELECT    * 
                    FROM    @TABLE 
                    WHERE   NAME='''+@NAME+''''
PRINT   @SQL

But I can't get any results or error. does anybody to sort this problem.

Upvotes: 2

Views: 13002

Answers (4)

A.J. Schroeder
A.J. Schroeder

Reputation: 411

Rather than concatenating the string together, I added a set statement to replace the @NAME variable with either the string value or IS NULL.

DECLARE @TABLE TABLE(ID INT,NAME VARCHAR(10))
INSERT INTO @TABLE
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,NULL

DECLARE @NAME VARCHAR(20)=NULL,
        @SQL VARCHAR(MAX)
        SET @SQL='SELECT    * 
                    FROM    @TABLE 
                    WHERE   NAME [NAME]'
set @SQL = replace(@SQL, '[NAME]', isnull('= ''' + @NAME + '''', 'IS NULL'))
print   @SQL

Upvotes: 1

DK5
DK5

Reputation: 302

There are two issues with the script

1) You set your variable @Name to NULL. Now when you concatenate a NULL with a VARCHAR variable, your result becomes a NULL string. So as your variable @SQL containing the SELECT statement is concatenated with @Name, it becomes NULL and on using it with EXECUTE your answer will return nothing.

2) You are declaring a table variable, which is out of the scope of the EXECUTE statement. When you use variables with dynamic SQL, they have to be either:

  1. Concatenated with the string like you attempted doing with @Name variable, in which case the value of the variable is passed and the value becomes a part of the string instead of the variable DECLARE @Name VARCHAR(20) = 'ABC'; EXEC('SELECT ''' + @Name + ''';');

The above statement will execute the following SQL equivalent:

SELECT 'ABC';
  1. The other way to use a variable as a variable from within the dynamic SQL statement is to declare that variable within the dynamic SQL only. Otherwise the variable is not recognized within the dynamic SQL and you get an a variable undeclared error on executing your SQL string. As you can see when you execute your statement, SQL will be unable to recognize the table variable @Table from within. The workaround for this is declaring the @Table variable inside the dynamic SQL string and initializing it there: DECLARE @NAME VARCHAR(20)=NULL, @SQL VARCHAR(MAX); SET @SQL='DECLARE @TABLE TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @TABLE SELECT 1,''A'' UNION SELECT 2,''B'' UNION SELECT 3,NULL; SELECT * FROM @TABLE WHERE NAME='''+@NAME+'''';

One important thing to remember is that, the Dynamic SQL string will be executed in a separate batch from the one in which it was called. What this means is that if we declare the @Table variable inside the dynamic SQL string then it will exist only for the duration and scope of the dynamic SQL string and will not exist after that. So any changes you make to the @Table variable will not be present after the dynamic SQL statement as the @Table variable will no longer exist. Similarly declaring the @Table variable outside the SQL string will prevent its usage in the dynamic @SQL batch.

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You can't access table variable in EXEC statement. Because the table variable is specific to scope of connection. And Exec statement will execute the code in another session(connection).

So instead you can use Temporary table (#)

CREATE TABLE #TABLE (ID INT,NAME VARCHAR(10))
INSERT INTO #TABLE
SELECT 1,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,NULL

DECLARE @NAME VARCHAR(20)=NULL,
        @SQL VARCHAR(MAX)
        SET @SQL='SELECT    * 
                    FROM    #TABLE 
                    WHERE  NAME '
SELECT @SQL = @SQL + CASE WHEN @NAME IS NULL THEN 'IS NULL' ELSE '='''+@NAME+'''' END
PRINT @SQL

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

When you concatenate a NULL with a string, you will always get a NULL only. Here the variable @NAME is NULL, so @SQL will also be NULL.

You can use the ISNULL function to assign a default value when @NAME is NULL. 

SET @SQL='SELECT    * 
            FROM    @TABLE 
            WHERE   ISNULL(NAME,'''')='''+ISNULL(@NAME,'')+''''

But another problem in your query is, you can't add a @Table_Variable inside a dynamic sql, it throw the error when you execute the query, but you can use #Temp_tables here.

Upvotes: 0

Related Questions