CODEWITHSUNDEEP
sql-serverstring
Ehsan Akbar
Ehsan Akbar

Reputation: 7281

Append string in SQL Server

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.

Upvotes: 0

Views: 60

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

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;

Upvotes: 1

Related Questions