How concatenate variable into a string query with t-sql

Question

I do not understand why my request does not appear with the print and suddenly, that it does not execute. For your information, @month returns one value '202205'.

my t-sql code :

use rh_usc

declare @month as varchar(100)
declare @query as varchar(max)

set @month += @month(select [Affichage période (AAAAMM)] from employees)
print @month

if object_id('employees_202205') is not null drop table [dbo.employees_202205]

set @query = 'select * into [employees_' + @month + '] from [employees]'
print @query
exec(@query)

Could you please help me?

Best regards

squillman · Accepted Answer · 2022-08-05 12:55:17Z

2

In this example, @month will actually be NULL since you don't initialize it when you declare it, nor do you account for NULL in the concatenation. Concatenating anything with NULL will return NULL.

DECLARE @month AS VARCHAR(100);
SET @month += @month + (SELECT [Affichage période (AAAAMM)] from employees);
PRINT @month;

Since @month is NULL to start, this concatenation will yield NULL and the print statement won't output anything.

If you change the first line to

declare @month as varchar(100) = '';

then you should get a result, assuming there is a value in the table.

Better yet, account for a NULL value in the code. CONCAT checks for NULL values.

DECLARE @month AS VARCHAR(100);
DECLARE @query AS VARCHAR(MAX);

SET @month = CONCAT(@month, (SELECT [Affichage période (AAAAMM)] FROM employees));
PRINT @month;

IF OBJECT_ID('employees_202205') IS NOT NULL DROP TABLE [dbo.employees_202205];

SET @query = 'select * into [employees_' + ISNULL(@month, '') + '] from [employees]';
PRINT @query;
EXEC (@query);

Note: In the above examples I also fixed a syntax error in your code in the first SET statement

SET @month += @month(SELECT [Affichage période (AAAAMM)] FROM employees)

will set @month and then execute a SELECT as an individual statement.

edited Aug 5, 2022 at 12:55

answered Aug 5, 2022 at 12:20

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

12 Comments

Christophe C. Over a year ago

my print commande returns now : select * into [employees_] from [employees]

squillman Over a year ago

Look at my full example that accounts for a NULL value for @month and also fixes the syntax error in your code.

Christophe C. Over a year ago

Yet when I run the command SELECT distinct [Display period (YYYYMM)] FROM employees, the result tells me 202205

squillman Over a year ago

If you ran the code I posted it shouldn't have. I did update it again because using CONCAT is a better way to do it.

squillman Over a year ago

Is this the full code you're working with though? Because, really, you don't need to concatenate @month at all in your example. You could just use SET @month = ISNULL((SELECT [Affichage période (AAAAMM)] FROM employees), ''); If this is a snippet from a bigger block of code and you initialize @month earlier then CONCAT is what to use.

|

Collectives™ on Stack Overflow

How concatenate variable into a string query with t-sql

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related