Written by

Packiaraj Santhiyagu

Share

  

Category

see all categories

Auto Query Generator for Stored Procedure in MSSQL Server

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.

Packiaraj Santhiyagu

Written by 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.