Auto Query Generator In MSSQL Server

 

If you’re a developer, irrespective of the platform, you  have to work with databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially when dealing with gigantic tables that have hundreds of columns. Writing SQL statements manually every time becomes a tiresome process.

Before explaining the script, I want to share the reason to write this script and how it is helping my peers. We have code standard on the database side. Below points are standards.

• Need to maintain a separate stored procedure to every table
• Don’t use * in the query instead specify the column
• Use the correct data type and size of a column
• Every parameter should be nullable in a stored procedure.

I am developing an application which is related to machines using .NET and SQL Server. The database design consists of some master tables and transactional tables. All the transactional table has more than 30 columns.

To meet my code standards, I need to mention all columns with correct data type and size in stored procedure parameters like below,

CREATEproc [dbo].[USP_PCNitemCreation] ( @Id int, @machineName varchar(50)=NULL, @furnacename varchar(50)=NULL, @minValue int=NULL, @maxValue int=NULL, @createdDate datetime=nullvarchar(100)=NULL )

All the queries should specify the column instead of using the start(*).

select machineName,furnacename from trn_furnace where Id=@Id

It consumes more time and is a boring task. So, I plan to write the script to is cut down on the time it takes and boring repeated work. We cannot automate the logic, but we can automate the repeated task.

Then I write the below script which really cuts down on all of our above pain points.

Auto Query Generator Stored Procedure for MSSQL Server,

CREATEproc [dbo].[USP_QuerycreationSupport] ( @table_Name varchar(100)=NULL ) AS
BEGINDECLARE @InserCols   NVARCHAR(max)DECLARE @Inserparam  NVARCHAR(max)DECLARE @Insertquery NVARCHAR(max)DECLARE @Selectquery NVARCHAR(max)DECLARE @Update      NVARCHAR(max)DECLARE @DeleteQuery NVARCHAR(max)
  -- sp paramSELECT '@'+c.NAME+Space(1)+Casecast(t.Nameasnvarchar(40))WHEN'nvarchar'THEN
  t.NAME    +'('+cast(c.max_length asnvarchar(30))+')'
WHEN'varchar'THEN
  t.NAME+'('+cast(c.max_length asnvarchar(30))+')'
WHEN'char'THEN
  t.NAME+'('+cast(c.max_length asnvarchar(30))+')'
WHEN'decimal'THEN
  t.NAME        +'(18,2)'
  ELSE t.nameend+'=null,'AS colss FROM sys.columns c innerjoin sys.types t ON c.user_type_id = t.user_type_id leftouterjoin sys.index_columns ic ON ic.object_id= c.object_idand ic.column_id = c.column_id leftouterjoin sys.indexes i ON ic.object_id= i.object_idand ic.index_id = i.index_id WHERE c.object_id=object_id(@table_Name)SELECT'Insert query'SET @InserCols=(selectdistinct
  (
         select sc.NAME+','
         FROM   sys.tables st innerjoinsys.columns sc
         ON st.object_id= sc.object_id
         WHERE  st.NAME= @table_Name forxmlpath(''),
                type).value('.','NVARCHAR(MAX)'))
  -- Return the result of the functionSELECT @InserCols=LEFT(@InserCols,Len(@InserCols)-1)
  --select @InserColsSET @Inserparam=(selectdistinct
  (
         select'@'+sc.NAME+','
         FROM   sys.tables st innerjoinsys.columns sc
         ON st.object_id= sc.object_id
         WHERE  st.NAME= @table_Name forxmlpath(''),
                type).value('.','NVARCHAR(MAX)'))
  -- Return the result of the functionSELECT @Inserparam=LEFT(@Inserparam,Len(@Inserparam)-1)
  --select @InserparamSET @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'SELECT @InsertquerySELECT'Update Query'SET @Update=(selectdistinct
  (
         select sc.NAME+'=@'+sc.NAME+','
         FROM   sys.tables st innerjoinsys.columns sc
         ON st.object_id= sc.object_id
         WHERE  st.NAME= @table_Name forxmlpath(''),
                type).value('.','NVARCHAR(MAX)'))
  -- Return the result of the functionSELECT @Update=LEFT(@Update,Len(@Update)-1)
  --select @UpdateSET @Update='UPdate '+@table_Name+' set '+@UpdateSELECT @Update
  -- For select QuerySELECT'Select Query'SET @Selectquery='select '+@InserCols +' from '+ @table_NameSELECT @Selectquery
  -- For Delete QuerySELECT'Delete Query'SET @DeleteQuery='delete from '+ @table_NameSELECT @DeleteQuery
end

How to use this script,

• Step 1 - Create the stored procedure using the above code or attached code.
• Step 2 - Execute the stored procedure and pass your table name as a parameter.

Exec USP_QuerycreationSupport@table_Name='mstCustomer'

Should not pass the database object in the table name

Exec USP_QuerycreationSupport@table_Name='[dbo].[mstCustomer]'

Once you execute the Stored Procedure 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 could this Auto Query Generator benefit you?

• 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 comes in handy while dealing with a table that has hundreds of columns

I hope this article helps you. Please comment below, if you have any query on this article.