Append string in SQL Server

Question

I want to execute this query as you can see :

DECLARE @site_value INT;
SET @site_value = 1310;

WHILE @site_value <= 1396
BEGIN
  ALTER DATABASE AdventureWorksDW
  ADD FILE
  (NAME = N'data_2002',

  FILENAME = N'C:\symfadb2filegroup\data_'+@site_value+'.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup_2002]

   SET @site_value = @site_value + 1;
END;

But I get this error in this part +@site_value

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '+'.

I used CONVERT and CONCAT but I get the same error.

Try to concat "C:\symfadb2filegroup\data_'+@site_value+'.ndf'," in variable and assign this variable to FileName — Dipak Delvadiya
– Dipak Delvadiya, Commented Aug 2, 2017 at 5:25

TheGameiswar · Accepted Answer · 2017-08-02 05:26:31Z

1

you will need to use dynamic sql like below

 set @Sql=' ALTER DATABASE AdventureWorksDW
  ADD FILE
  (NAME = N''data_2002''

  FILENAME = N''C:\symfadb2filegroup\data_'''+cast(@site_value  as varchar(4))+'.ndf'',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup_2002]'
  print @Sql

total code below

DECLARE @site_value INT;
SET @site_value = 1310;

declare @Sql nvarchar(max)

WHILE @site_value <= 1396
BEGIN
 set @Sql=' ALTER DATABASE AdventureWorksDW
  ADD FILE
  (NAME = N''data_2002''

  FILENAME = N''C:\symfadb2filegroup\data_'''+cast(@site_value  as varchar(4))+'.ndf'',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup_2002]'
  print @Sql
  exec(@sql)


   SET @site_value = @site_value + 1;
END;

answered Aug 2, 2017 at 5:26

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Append string in SQL Server

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