How to create Dynamic SQLs via Stored Procedure3 min read

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.

Auto Query Generator Stored Procedure for MSSQL Server


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’

NB: You shouldn’t pass like [dbo].[mstApps]

Once you execute the StoredProcedure as mentioned above, you get all the SQL statements as shown here. You could easily use the generated SQL statements elsewhere. You get all basic SQL statements like Select, Insert, Update & Delete.

How to create sql via stored procedure

How this Auto Query Generator could benefit?

  • Minimizes your time in Query Creation
  • Eliminates Human errors in datatype mismatches, size etc.
  • Irrespective of Table size, you get all basic SQL instantly
  • Especially come handy while dealing with a table that has 100’s of columns

You could find more such interesting solutions on our blog.

About the author

Packiaraj Santhiyagu

Packiaraj, we rechristened him ‘Bahubali’, although not for his muscular attire or his ability to confront elephants, but his persistence to find a solution for every SharePoint issue he encounters. A cool guy who responds even to grave situations with his calm smile. He has been with Hubfly and the product development since its inception.

Leave a Reply

Your email address will not be published. Required fields are marked *