turbo88 turbo88 - 2 months ago 15
SQL Question

Dynamic SQL Server query

I am writing a dynamic insert query in a stored procedure. I am receiving the column names as parameter to my stored procedure.

For example, I have an

Employee
table with
EmployeeId
and
EmployeeName
columns. I need to append
EMP_
before each employee name while inserting the data into
Department
table from
Employee
table.

Non-dynamic query looks like this.

INSERT INTO Department(EmployeeId, EmployeeName)
SELECT
EmployeeId, 'EMP_' + EmployeeName
FROM
Employee


If I write a dynamic insert

SET @SqlCommand =
'INSERT INTO ' + @DepartmentTable + '(' + @EmployeeIdColumn + ',' + @EmployeeNameColumn + ')' +
'SELECT ' + @EmployeeIdColumn + ',''EMP_''' + @EmployeeNameColumn + '''' +
'FROM ' +
@EmployeeTable + ' WTB '

EXEC sp_executesql
@stmt = @SqlCommand


The issue is, for the
EmployeeName
column, it is inserting "EMP_EmployeeName" instead of actual employee name. I tried putting quotes before and after
EmployeeNameColumn
, but it didn't work. How can I fix it?

Answer

You need just one + after EMP_'', and remove it after ' WTB '. Also i fixed some spaces:

SET @SqlCommand =
'INSERT INTO ' + @DepartmentTable + ' (' + @EmployeeIdColumn + ',' + @EmployeeNameColumn + ')' +
' SELECT ' + @EmployeeIdColumn + ',''EMP_''+' + @EmployeeNameColumn +      
' FROM ' + @EmployeeTable + ' WTB ' 

If you PRINT @SqlCommand you will get something like this:

INSERT INTO Department (EmpId,EmpName) SELECT EmpId,'EMP_'+EmpName FROM Employee WTB 

And one note: better use QUOTENAME with table/column names. It will help to avoid situations where column has spaces in the name like 'Employee Name':

SET @SqlCommand =
'INSERT INTO ' + QUOTENAME(@DepartmentTable) + '(' + QUOTENAME(@EmployeeIdColumn) + ',' + QUOTENAME(@EmployeeNameColumn) + ')'+
' SELECT ' + QUOTENAME(@EmployeeIdColumn) + ',''EMP_''+' + QUOTENAME(@EmployeeNameColumn) + 
' FROM ' + QUOTENAME(@EmployeeTable) + ' WTB '

To get:

INSERT INTO [Department]([EmpId],[EmpName]) SELECT [EmpId],'EMP_'+[EmpName] FROM [Employee] WTB