How to pass default value in dynamic SQL query if column not exists in a table

Question

I have a dynamic SQL query which selects table name based on user type. Here both Admin & User tables have similar columns but Admin table have one additional column 'Col4'.

Now I need to pass this additional column value if the selected table is AdminTable else I need to pass default hard-coded value in below dynamic sql query.

Is there any SQL keyword or any simple way to achieve this functionality?

IF @UserType = 'Admin'                                                                                   
 SET @TableName = 'AdminTable'   
ELSE IF @UserType = 'User'                        
 SET @TableName = 'UserTable' 

SET @SqlQuery = 'SELECT Col1,Col2,Col3 FROM '+@TableName+' WITH(NOLOCK)'
EXEC (@SqlQuery)

Answer: Below one works for me.

SET @SqlQuery = 'SELECT Col1,Col2,Col3,Case When COL_LENGTH(''@TableName'', ''Col4'') IS NOT NULL THEN Col4 Else 0 END as AdminRole FROM '+@TableName+' WITH(NOLOCK)'
EXEC (@SqlQuery)

What do you mean by " pass this additional column value if the selected table is AdminTable". Do you want to have a where clause with Col4 or you want to select col4 when usertype is 'Admin'? — AB_87
– AB_87, Commented Jul 12, 2018 at 5:44
@AB_87 I want to select col4 when usertype is Admin otherwise I need to pass some default value in the select list. — Tech Learner
– Tech Learner, Commented Jul 12, 2018 at 5:48

AswinRajaram · Accepted Answer · 2018-07-12 05:53:08Z

2

IF @UserType = 'Admin'                                                                                   
 SET @SqlQuery = 'SELECT Col1,Col2,Col3,Col4 FROM '+@UserType+'Table'+' WITH(NOLOCK)'
ELSE IF @UserType = 'User'                        
 SET @SqlQuery = 'SELECT Col1,Col2,Col3 FROM ' +@UserType+'Table'+' WITH(NOLOCK)' 

EXEC (@SqlQuery)

Guess you have to do it this way as an additional column is involved.

answered Jul 12, 2018 at 5:53

AswinRajaram

1,64211 silver badges20 bronze badges

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

Comments

AB_87 · Accepted Answer · 2018-07-12 06:21:19Z

1

You can simply change your query text based on condition.

DECLARE @DefaultValue INT = 10
IF @UserType = 'Admin'
    SET @SqlQuery = 'SELECT Col1,Col2,Col3, Col4 FROM AdminTable WITH(NOLOCK)' ;    
ELSE IF @UserType = 'User'
    SET @SqlQuery = 'SELECT Col1,Col2,Col3, ' + CAST(@DefaultValue AS VARCHAR(10)) + ' as C4 ' + 'FROM UserTable WITH(NOLOCK)';


EXEC ( @SqlQuery );

edited Jul 12, 2018 at 6:21

answered Jul 12, 2018 at 5:43

AB_87

1,1568 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to pass default value in dynamic SQL query if column not exists in a table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related