Why won't my query in a dynamic SQL work as intended?

发布于 2020-03-27 10:17:39

I believe I have a formatting problem in the query but I need a second set of eyes to help me please.

select @InsertSQL = ' Insert Into #ResultSet('
    +   'UserDomainMappingId,'
    +   'UserId,'
    +   'UserName,'
    +   'Domain,'
    +   'DomainName,'
    +   'LastUpdatedBy,'
    +   'LastUpdatedByName,'
    +   'LastUpdatedAt)'

select @SelectSQL = ' select '
    +   'UD.UserDomainMappingId,'
    +   'UD.UserId,'
    +   '(select FullName from Users where UserId = UD.UserId),'
    +   'UD.Domain,'
    +   '(select Name from ReferenceCodes RC where RC.Type = ''DOMAIN'' and RC.Code = ''' + @Domain + '''),'
    +   'UD.LastUpdatedBy,'
    +   '(select FullName from Users where UserId = UD.LastUpdatedBy),'
    +   'UD.LastUpdatedAt'                              

    select @FromSQL = ' from UserDomainMapping UD '

    select @WhereSQL = 'where UserDomainMappingId = UD.UserDomainMappingId '

I'm expecting:

the select for DomainName to work, as it does if I remove the quotes and hardcode the scaler, but it doesn't inside the dynamic sql. Any ideas? Thanks.

lakta 2019-07-03 22:14

Concatenating string in SQL has a tendency to NULL the result if any of the values is NULL. Made a little example you can try:

declare @A varchar(100), @B varchar(100), @C varchar(100)

set @A = 'a'
set @B = 'b'
set @C = @A + @B 
select @C --'ab' 

set @A = null
set @B = 'b'
set @C = @A + @B 
select @C --NULL 

set @A = 'a'
set @B = null
set @C = @A + @B 
select @C --NULL 

set @A = 'a'
set @B = null
set @C = @A + isnull(@B ,'')
select @C --'a'

So, basically you get an empty query, because @Domain is NULL. For that I suggest to use isnull(@Domain,'')

H22 2019-07-03 22:27:53

I overlooked that @Domain is Null. It Shouldn't be and that's the problem. Thank you.

Related issues

Query is possibly slow?

Simple Sql Role query issue

Group by query with rollup

How to include a PHP variable inside a MySQL statement

Getting empty rows from "SELECT ALL" query in PostgreSQL

MySQL pivot table using java

Implement smart search by text field in database

How to speed up count(distinct) with Between clause in MySQL

how to perform sql query MIN/MAX FILTER

Dynamic pivot in oracle sql