Auto Query Generator In MSSQL Server

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.

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)

    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 Params
FROM sys.columns c
        inner join sys.types t
            ON c.user_type_id = t.user_type_id
    WHERE c.object_id = object_id(@table_Name)

    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)')
    )
    SELECT @InserCols = LEFT(@InserCols, Len(@InserCols) - 1)

    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)')
    )


    SELECT @Inserparam = LEFT(@Inserparam, Len(@Inserparam) - 1)

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

    SELECT @Insertquery AS InsertQuery


    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)')
    )

    SELECT @Update = LEFT(@Update, Len(@Update) - 1)

    SET @Update = 'Update ' + @table_Name + ' set ' + @Update

    SELECT @Update AS UpdateQuery

    SET @Selectquery = 'select ' + @InserCols + ' from ' + @table_Name

    SELECT @Selectquery AS SelectQuery
END

--Execute the stored procedure and pass your table name as a parameter.
EXECUTE dbo.[USP_QueryCreationSupport] @table_Name = 'settings'