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 EmployeeI

Solution 1:

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:

INSERTINTO 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:

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

Solution 2:

Try using the CONCAT function.

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

CONCAT ( string_value1, string_value2 [, string_valueN ] )  

Your query would then look like this :

INSERTINTO deptTable(empId,empName) 
SELECT empId,concat('EMP_', empName) as empName FROM empTable

PS: Looks like I misunderstood your question.

INSERTINTO deptTable(empId,empName) 
SELECT empId,'SUFFIX'+empName as empName FROM empTable

will also work. I guess you wanted it for the stored proc.

In any case the concat should still do it.

Solution 3:

Here is the dynamic query to create stored procedure with insert statement with columns and parameters

>DECLARE@table_name varchar(255) ='table_name'>DECLARE@v_col varchar(MAX)
>DECLARE@v_param varchar(MAX)
>DECLARE@SP_param varchar(MAX)
>DECLARE@sp_type varchar(10) ='Insert'>>SET@v_col = STUFF(
>                      (SELECT','+'['+c.name+']'>FROM sys.tables t  JOIN sys.columns c ON t.object_id = c.object_id
>WHERE t.name =@table_name
>AND c.is_identity =0>FOR XML PATH ('')), 1, 1, ''>                             )
>SET@v_param =  STUFF(
>                          (SELECT','+'@'+c.name
>FROM sys.tables t  JOIN sys.columns c ON t.object_id = c.object_id
>WHERE t.name =@table_name
>AND c.is_identity =0>FOR XML PATH ('')), 1, 1, ''>                             )
>>SET@SP_param = STUFF(
>                          (SELECT','+'@'+c.name+' '+y.name+' '+(CASEWHEN y.name ='varchar'THEN'('+CAST(c.max_length asvarchar)+')'>ELSE''END)
>FROM sys.tables t  JOIN sys.columns c ON t.object_id = c.object_id
>JOIN sys.types y ON y.user_type_id = c.user_type_id
>WHERE t.name =@table_name
>AND c.is_identity =0>FOR XML PATH ('')), 1, 1, ''>                             )
>Declare@QueryVARCHAR(MAX)
>DECLARE@SPQueryVARCHAR(MAX)
>SET@SPQuery='CREATE PROCEDURE '+@sp_type+''+@table_name+' ('+@SP_param+')
>       AS BEGIN'>SET@Query='Insert Into '+@table_name+' ('+@v_col+')
>         Values (
>             '+@v_param+')
>             END'>>>       PRINT(@SPQuery); 
>       PRINT(@Query);