Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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 '[email protected]_Name+'('[email protected]+')'+'values'+'('[email protected]+')'
	select @Insertquery

	select 'Update Query'
	SET @Update=  ( SELECT DISTINCT (SELECT   sc.NAME +'[email protected]'+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  '[email protected]_Name+'  set '[email protected]
	select @Update

	-- For select Query
	select 'Select Query'
	 
	set @Selectquery='select '[email protected] +' 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.

[email protected]_Name=’mstCustomer’

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

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.

The post Auto Query Generator for Stored Procedure in MSSQL Server appeared first on Hubfly.



This post first appeared on 7 MISCONCEPTIONS SMALL BUSINESS HAVE ABOUT INTRANET, please read the originial post: here

Share the post

Auto Query Generator for Stored Procedure in MSSQL Server

×

Subscribe to 7 Misconceptions Small Business Have About Intranet

Get updates delivered right to your inbox!

Thank you for your subscription

×