Dynamic SQL showing SQL statement NULL

Question

While running this code:

DECLARE @strSelectQuery NVARCHAR(MAX),
        @strColumnList  NVARCHAR(MAX),
        @strTempTable   NVARCHAR(50),
        @strMainTable   NVARCHAR(50)

SET @strTempTable = 'TempdimRevenueCode'
SET @strMainTable = 'dimRevenueCode'
SET @strColumnList = 'DBID,Revenue_Code_ID,Revenue_Code_AltID,revenue_code,revenue_code_description,BeginUTCDateTime,EndUTCDateTime,IsCurrent'

SELECT @strSelectQuery = COALESCE(@strSelectQuery,'UNION ALL') + ' UNION ALL ' + CHAR(13) 
                             + ' SELECT '+CONVERT(VARCHAR,DBID) +' as ' + @strColumnList + CHAR(13) 
                             + ' FROM ['+ InstanceName +'].['+DBName+'].dbo.'+@strMainTable + CHAR(13)
                             + ' WHERE BeginUTCDateTime  > ' + CHAR(13)
                             +'      (SELECT ISNULL( MAX(BeginUTCDateTime) ,''1900-01-01'') FROM  '+@strMainTable +  ' WHERE DBID='+CONVERT(VARCHAR,DBID)+')'+CHAR(13) 
                             +' OR    EndUTCDateTime  > ' + CHAR(13)
                             +'      (SELECT ISNULL( MAX(BeginUTCDateTime) ,''1900-01-01'') FROM  '+@strMainTable +  ' WHERE DBID='+CONVERT(VARCHAR,DBID)+')'+CHAR(13) 
FROM adminODSDBDetails 
WHERE Active_TF = 1 and ClientProductName='GEMINI'

SELECT @strSelectQuery

EXECUTE sp_executesql   @strSelectQuery

When selecting the dynamic statement (select @strSelectQuery), it is shown as NULL.

If I remove ClientProductName='GEMINI' from the dynamic code and select @strSelectQuery, it is printing a valid SQL statement.

Is there a record in the table adminODSDBDetails where ClientProductName='GEMINI'? Since you are adding strings it is enough that one of them is null to make it all NULL.I would recommend using ISNULL on each column from the table. — cte6
– cte6, Commented Sep 18, 2019 at 5:17
What results do you get from SELECT * FROM adminODSDBDetails WHERE Active_TF = 1 and ClientProductName='GEMINI'? — Zohar Peled
– Zohar Peled, Commented Sep 18, 2019 at 5:28
@ZoharPeled No result iam having actual result other than ClientProductName='GEMINI' in future it may come — Chanukya
– Chanukya, Commented Sep 18, 2019 at 5:29

Michał Turczyn · Accepted Answer · 2019-09-18 05:40:37Z

1

This can be caused by two factors:

either additional condition in where causes the query to return no results (no records satisfy all condition with that one)
or it returns record(s), but one of the value in that record is null, which makes whole statement evalute to null

so I suggest running query alone:

SELECT DBID,InstanceName,DBName
FROM adminODSDBDetails 
WHERE Active_TF = 1 and ClientProductName='GEMINI'

without assigning it to a variable and without extra text and see what is the reason.

answered Sep 18, 2019 at 5:40

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Dynamic SQL showing SQL statement NULL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related