Create Dynamic SQLs via Stored Procedure

If you’re a developer, irrespective of the platform, you would have to work with Databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially with dealing gigantic tables that have hundreds of columns.

Writing SQL statements manually every time becomes a tiresome process. But we have a solution. You could write a Stored Procedure to automatically generate the queries. We have attached the Stored Procedure code for MSSQL Server, though you can replicate to any database with minor changes.

CREATE proc [dbo].[USP_QuerycreationSupport]
(
@table_Name varchar(100)

)
as
begin
DECLARE @InserCols NVARCHAR(MAX)
DECLARE @Inserparam NVARCHAR(MAX)
DECLARE @Insertquery NVARCHAR(MAX)
DECLARE @Selectquery NVARCHAR(MAX)
DECLARE @Update NVARCHAR(MAX)
DECLARE @DeleteQuery NVARCHAR(MAX)

 -- sp param

 SELECT 
    '@'+c.name+ SPACE(1) + case cast(t.Name as nvarchar(40))   when 'nvarchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')' 
                  when 'varchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'char'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'decimal' then t.Name+'(18,2)' else t.Name end +'=null,' as colss
   
   
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@table_Name)




 select 'Insert query'
 SET @InserCols=  ( SELECT DISTINCT (SELECT   sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @InserCols=LEFT(@InserCols,LEN(@InserCols)-1)
 --select @InserCols

 
 SET @Inserparam=  ( SELECT DISTINCT (SELECT   '@'+sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Inserparam=LEFT(@Inserparam,LEN(@Inserparam)-1)
 --select @Inserparam

 set @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'
 select @Insertquery

 select 'Update Query'
 SET @Update=  ( SELECT DISTINCT (SELECT   sc.NAME +'=@'+sc.NAME+',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Update=LEFT(@Update,LEN(@Update)-1)
 --select @Update
 SET @Update='UPdate  '+@table_Name+'  set '+@Update
 select @Update

 -- For select Query
 select 'Select Query'
  
 set @Selectquery='select '+@InserCols +' from '+ @table_Name
 select @Selectquery

 -- For Delete Query
 select 'Delete Query'
  
 set @DeleteQuery='delete from  '+ @table_Name
 select @DeleteQuery
end

 --   exec USP_QuerycreationSupport @table_Name='MST_ComboMain'
 

Step 1: Create the StoredProcedure. The attached file contains the code for creating a Stored Procedure that auto-generates SQL Queries.

Step 2: Execute the StoredProcedure, passing your required table name as a parameter.

execUSP_QuerycreationSupport@table_Name=’mstCustomer’