"medium generate-tsql-stored-procedures" Code Answer's

medium generate-tsql-stored-procedures

By Frightened Fish on Mar 15, 2021

CREATE proc [dbo].[USP_QuerycreationSupport](@table_Name varchar(100))asbeginDECLARE @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) + 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 cINNER JOIN     sys.types t ON c.user_type_id = t.user_type_idLEFT OUTER JOIN     sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_idLEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_idWHERE    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 @InserColsSET @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 @Inserparamset @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'	select @Insertqueryselect '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 @DeleteQueryend--   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.

Source: medium.com

Add Comment