T-SQL Syntax error

Question

Hi Team,

I am working on dynamically restore DB.

As part of this i have created store procedure.

While running getting below syntax error:

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near 'dba_db'.

Msg 132, Level 15, State 1, Line 13

The label 'L' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 14

The label 'L' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 15

The label 'L' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 16

The label 'L' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'L' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 18

	DECLARE @backup_files varchar(8000)
exec ABC.dbo.pr_RESTORE_STRING_FOLDER '\\1.2.3.4\dba\FULL\', 'dba_db', @backup_files OUTPUT


DECLARE @SQL VARCHAR(8000);

SET @SQL = @backup_files + CHAR(10) + 'WITH
	
	
	MOVE 'dba_db' TO 'L:\DATA\CAERPT1D\dba_db_NEW\dba_db.mdf' ,
	MOVE 'dba_db_log' TO 'L:\LOG\CAERPT1D\dba_db_NEW\dba_db_log.ldf' ,
	MOVE 'dba_db_FG_IX' TO 'L:\DATA\CAERPT1D\dba_db_NEW\dba_db_FG_IX.ndf' , 
	MOVE 'dba_db_FG_PK_DEF' TO 'L:\DATA\CAERPT1D\dba_db_NEW\dba_db_FG_PK_DEF.ndf' ,
	MOVE 'dba_db_FG_PK1_worlf' TO 'L:\DATA\CAERPT1D\dba_db_NEW\dba_db_FG_PK1_worlf.ndf'

Answer 1

I am working on dynamically restore DB.

That's already not the best idea and if you want to use dynamic SQL then start learning about it https://www.sommarskog.se/dynamic_sql.html

SET @SQL = @backup_files + CHAR(10) + 'WITH

That code is more then wrong. If the dynamic SQL should contain hyphens ('), then you have to quote them.

... and much more issues with your code, e.g. missing spaces.

Use PRINT command to get the content of the variable @ SQL to validate it.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql?view=sql-server-ver16

Answer 2

Hi @Vijay Kumar

Like Olaf said, your code confuses us.

Dynamic SQL is a difficult syntax. You can refer to this document to improve your code.

https://www.sqlshack.com/learn-sql-dynamic-sql/

Best regards,

Percy Tang