How to set a create table statement into a variable in SQL Server?

Question

When I use to execute and write the direct table creation query, it works fine. But when I set the same SQL to a variable and then execute the variable, it does.

EXECUTE ( 'IF NOT EXISTS (SELECT * FROM sys.objects 
                          WHERE object_id = OBJECT_ID(N''DQ_ACC_' + @BUSINESS_NAME + '_OP_BASIC_PROFILE_ENTITY_TREND'') AND type = ''U'')
               CREATE TABLE DQ_ACC_' + @BUSINESS_NAME + '_OP_BASIC_PROFILE_ENTITY_TREND
               (
                   [BUSINESS_NAME] [VARCHAR](200) NULL,
                   [PROFILE_NUM] [INT] NULL,
                   [TABLE_NAME] [VARCHAR](200) NULL,
                   [ATTRIBUTE_COUNT] [INT] NULL,
                   [MIN_ROW_LENGTH] [INT] NULL,
                   [MAX_ROW_LENGTH] [INT] NULL,
                   [NUM_DUPLICATE_RECS] [INT] NULL,
                   [SUMMARY_DETAIL_VIEW] [VARCHAR](100) NULL,
                   [S_KEY] [INT] NULL,
                   [LOAD_TIMESTAMP] [DATETIME] NULL) ON [PRIMARY]'
            );

When I assign the same query using the set command, it does not work.

Can you please tell me what I am doing wrong here?

first you could check the @BUSINESS_NAME whether it has value or null ? cause if null it will not generate the script. — Yogesh Sharma
– Yogesh Sharma, Commented Nov 9, 2017 at 7:11

Jayasurya Satheesh · Accepted Answer · 2017-11-09 07:02:11Z

try this

DECLARE @Qry VARCHAR(MAX) = 'IF OBJECT_ID(N''DQ_ACC_' + @BUSINESS_NAME + '_OP_BASIC_PROFILE_ENTITY_TREND'' ) IS NULL
                            CREATE TABLE DQ_ACC_' + @BUSINESS_NAME + '_OP_BASIC_PROFILE_ENTITY_TREND
                                    (
                                        [BUSINESS_NAME] [VARCHAR](200) NULL,
                                        [PROFILE_NUM] [INT] NULL,
                                        [TABLE_NAME] [VARCHAR](200) NULL,
                                        [ATTRIBUTE_COUNT] [INT] NULL,
                                        [MIN_ROW_LENGTH] [INT] NULL,
                                        [MAX_ROW_LENGTH] [INT] NULL,
                                        [NUM_DUPLICATE_RECS] [INT] NULL,
                                        [SUMMARY_DETAIL_VIEW] [VARCHAR](100) NULL,
                                        [S_KEY] [INT] NULL,
                                        [LOAD_TIMESTAMP] [DATETIME] NULL
                                    ) ON [PRIMARY]'


            );

EXEC(@Qry)

Yogesh Sharma · Accepted Answer · 2017-11-09 07:24:12Z

Here i have set value @BUSINESS_NAME to 1, then it will generate the create table SQL Script :

DECLARE @Q NVARCHAR(MAX), @BUSINESS_NAME NVARCHAR(MAX) = '1'

SELECT @Q = N'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''DQ_ACC_' + @BUSINESS_NAME + '_OP_BASIC_PROFILE_ENTITY_TREND'' 
                                                                 ) AND type = ''U'')
                            CREATE TABLE DQ_ACC_' + @BUSINESS_NAME + '_OP_BASIC_PROFILE_ENTITY_TREND
                                    (
                                        [BUSINESS_NAME] [VARCHAR](200) NULL,
                                        [PROFILE_NUM] [INT] NULL,
                                        [TABLE_NAME] [VARCHAR](200) NULL,
                                        [ATTRIBUTE_COUNT] [INT] NULL,
                                        [MIN_ROW_LENGTH] [INT] NULL,
                                        [MAX_ROW_LENGTH] [INT] NULL,
                                        [NUM_DUPLICATE_RECS] [INT] NULL,
                                        [SUMMARY_DETAIL_VIEW] [VARCHAR](100) NULL,
                                        [S_KEY] [INT] NULL,
                                        [LOAD_TIMESTAMP] [DATETIME] NULL
                                    ) ON [PRIMARY]'



 PRINT @Q
--EXECUTE sp_executesql @Q

however, if you do not specify the value @BUSINESS_NAME then it will not generate the script

Result :

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'DQ_ACC_1_OP_BASIC_PROFILE_ENTITY_TREND' 
                                                                 ) AND type = 'U')
                            CREATE TABLE DQ_ACC_1_OP_BASIC_PROFILE_ENTITY_TREND
                                    (
                                        [BUSINESS_NAME] [VARCHAR](200) NULL,
                                        [PROFILE_NUM] [INT] NULL,
                                        [TABLE_NAME] [VARCHAR](200) NULL,
                                        [ATTRIBUTE_COUNT] [INT] NULL,
                                        [MIN_ROW_LENGTH] [INT] NULL,
                                        [MAX_ROW_LENGTH] [INT] NULL,
                                        [NUM_DUPLICATE_RECS] [INT] NULL,
                                        [SUMMARY_DETAIL_VIEW] [VARCHAR](100) NULL,
                                        [S_KEY] [INT] NULL,
                                        [LOAD_TIMESTAMP] [DATETIME] NULL
                                    ) ON [PRIMARY]

Collectives™ on Stack Overflow

How to set a create table statement into a variable in SQL Server?

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