SQL Server Development Interface generation method

In order to improve development efficiency, it is necessary to generate a fixed-format interface, for example, to provide new/modified/delete/Read interfaces:

Take the common table structure for example, the special table structure can try to adjust the method

1, in the test database generation method

If object_id (' curdsqlstring ', ' P ') is not nulldrop PROC curdsqlstringgocreate PROCEDURE curdsqlstring (@TableName sysname       ) with Encryptionasif object_id (@TableName, ' U ') is Nullreturn 0DECLARE @S NVARCHAR (max), @ColName NVARCHAR (max)       , @Identity sysname, @IdentityWHERE NVARCHAR, @Insert0 NVARCHAR (max), @Insert1 NVARCHAR (max)       , @Insert2 NVARCHAR (max), @Insert3 NVARCHAR (max), @UPDATE0 NVARCHAR (max), @UPDATE1 NVARCHAR (max) , @UPDATE2 NVARCHAR (max), @UPDATE3 NVARCHAR (max), @SELECTWHERE0 NVARCHAR (max), @SELECTWHERE1 NVARCHAR ( Max), @SELECT0 NVARCHAR (max), @SELECT1 NVARCHAR (max), @DELETE NVARCHAR (max), @ColName2 NVARCHAR (MA X), @ObjectID INT, @UPDATECol1 NVARCHAR (max), @ColAll NVARCHAR (max), @InputCol sysname, @Wri Te NVARCHAR, @OutputAll NVARCHAR (+), @TableName2 sysnameselect @Write = ' Roy ' +char (+) +char (+) +char (+) +con Vert (varchar), GETDATE (),@Insert2 = ', @Insert3 = ' SELECT @s=case when a.is_computed=1 then @S ELSE ISNULL (@s+ ', ', ') + ' @ ' +name+char (+) + TYPE _name (user_type_id) +casewhen user_type_id in ( 34,35,36,48,52,56,58,59,60,61,62,98,99,104,122,127,189,241,256,241,40,41,129) then "when user_type_id in (106,108) Then ' (' +rtrim (Precision) + ', ' +rtrim [scale] + ') ' When user_type_id in (231,239) then case is Max_length=-1 then ' (max) ' E LSE ' (' +rtrim (MAX_LENGTH/2) + ') ' Endwhen max_length=-1 Then ' (max) ' Else ' (' +rtrim (max_length) + ') ' End+casewhen is_ Identity=1 then char + ' OUTPUT ' Else ' end end, @ColName =case if Is_identity=1 OR a.is_computed=1 then @ColName else i Snull (@ColName + ', ', ') +quotename (Name) end, @ColName2 =case when Is_identity=1 OR a.is_computed=1 then @ColName2 else                                 IsNull (@ColName2 + ', ', ') + ' @ ' +name end, @UPDATECol1 =case when Name!=n ' ID ' and not EXISTS (SELECT 1                     From sys.objects X joins sys.indexes y on x.type = N ' PK '                                         and x.name = Y.name JOIN Sysindexkeys z on                                                              Z.id = x.object_id and Z.indid = y.index_id                                                               and z.colid = a.column_id and x.object_id=a.object_id) and a.is_computed=0 then ISNULL (@UPDATECol1 + ', ', ') +quotename (Name) + ' = ' + ' @ ' +name E LSE @UPDATECol1 END, @IdentityWHERE =isnull (@IdentityWHERE, ') +case when ColumnProperty (object_id, Name, ' isidentity ') =1 OR name=n ' ID ' then QuoteName (name) + ' = ' + ' @ ' +name ELSE ' end, @ColAll =isnull (@ColAll + ', ', ') +quotename (name), @ Outputall=isnull (@OutputAll + ', ', ') + ' @ ' +namefrom sys.columns as Awhere a.object_id=object_id (@TableName) Order BY  Case if Name= ' ID ' then 0 else 1 end,column_idif @IdentityWHERE = "SELECT @IdentityWHERE = @IdentityWHERE +case when @IdentityWHERE > "and EXISTS (SELECT 1                                From sys.objects X joins sys.indexes y on x.type =                                        N ' PK ' and x.name = Y.name JOIN Sysindexkeys z on z.id = a.object_id and Z.indid =                                  y.index_id and z.colid = a.column_id) Then ' and '                                 ELSE ' END + case When EXISTS (SELECT 1                                                              From sys.objects X joins sys.indexes y on x.type = N ' PK ' and x.name = Y.name JOIN Sysinde                               Xkeys z on z.id = a.object_id and Z.indid = y.index_id                               and z.colid = a.column_id) then QUOTENAME (name) + ' = ' + ' @ ' + name ELSE ' END from Sys.columns as a WHERE object_id = object_id (@TableName) ORDER by Column_idif @IdentityWHERE = ' SELECT TOP 1 @IdentityWHERE =quotename (Name) + ' = ' + ' @ ' +name from sys.columns WHERE object_id =OBJECT_ID (@TableName) Order by column_id--primary key for first column select @Identity =replace (Left (@s,charindex (', ', @s)-1), char (32) + ' Output ', '), @ObjectID =object_id (@TableName), @TableName =replace (replace (Stuff (@TableName, 1,charindex ('. '), @ TableName), '), '] ', ' '), ' [', ' '), @TableName2 =case when @TableName '%.% ' and ' Then ' +object_schema_name (@ObjectID) + ‘.‘ [Email protected] ELSE @TableName endselect @Insert1 = ' CREATE PROCEDURE c ' [Email protected]+char (Ten) +char (9) +char (9) + ' (' [email  protected]+ ') ' +char + ' as ', @Insert2 = ' INSERT into ' [Email protected]+char (Ten) +char (9) + ' (' [email  protected]+ ') ', @Insert3 = ' VALUES ' +char (9) + ' (' [email protected]+ ') ' +casewhen exists (SELECT 1 from Sys.columns WHERE [email protected] and Is_identity=1) Then char (+char) + ' SET ' +left (@Identity, CHARINDEX (char (+), @Identity)-1) + ' =scope_identity () ' Else ' end,@ update1= ' CREATE PROCEDURE u ' [Email protected]+char (Ten) +char (9) +char (9) + ' (' +replace (@s,char + ' output ', ') + ') ' +char + ' as ', @UPDATE2 = ' UPDATE ' [Email protected]+char] + ' SET ' [email protected], @UPDATE3 = ' WHERE ' [email protected], @SELECTWHERE1 = ' CREATE PROCEDURE r ' [email protected]+ ' by ' +stuff (left (@Identity, CHARINDEX (char (1), @Identity)-), +char (+) +char (9) +char (9) + ' (' [email protected]+ ') ' +char (+) + ' as ' + char (Ten) + ' SET NOCOUNT on; ' +char (Ten) + ' SET TRANSACTION isolation level READ uncommitted; ' +char (+) + ' SELECT ' +char (+char) (9) [Email protected]+char (+) + ' from ' +char (9) +char Protected]+char (Ten) + ' WHERE ' +char (9) [email protected], @SELECT1 = ' CREATE PROCEDURE R ' [email  protected]+ ' ForAll ' +char (10) + 'As ' +char + ' SET NOCOUNT on; ' +char (Ten) + ' SET TRANSACTION isolation level READ uncommitted; ' +char (+) + ' SELECT ' +char (+char) (9) [Email protected]+char (+) + ' from ' +char (9) +char Protected], @DELETE = ' CREATE PROCEDURE d ' [Email protected]+char (Ten) +char (9) + ' (' [email protected]+ ') ' + char (+) + ' as ' +char (+) + ' DELETE ' [Email protected]+char (+) + ' WHERE ' +char (+) [email protected],@  Inputcol=case when exists (SELECT 1 from Sys.columns WHERE [email protected] and Is_identity=1) then @Identity Else ' End--insertselect @Insert0 = ' If object_id (' +quotename (' C ' [email protected], ' ") + ', ' P ') is not NULL ' +char (13) +char (9) + ' DROP PROC ' +quotename (' C ' [email protected]) +char (Ten) + ' Go ' +char (+ +char) + '/' +replicate (' * ', 160 ) +char (10) + '% of the stored procedure name: C ' [Email protected]+char (Ten) +char (10) + ' percent input parameter: ' [Email protected]+char] +char ( 10) + '% output parameter: ' [Email protected]+char (10) + ' percent of +char ' function: Added record in table ' [Email protected]+char (Ten) +replicate (' * ', +char (10) + ' percent of percent write: '[Email protected]+char (+char) +replicate (' * ') + '/',--update@update0= ' if object_id (' +quotename (' u ' [ Email protected], "') + ', ' P ') is not NULL ' +char (+char) (9) + ' DROP PROC ' +quotename (' u ' [email protected] ) +char + ' Go ' +char (+) +char (+) + '/' +replicate (' * ', ') +char (10) + '% stored procedure name: U ' [Email protected]+char (10) + char (10) + ' percent input parameter: ' [Email protected]+char (10) + '% ' output parameter: ' +char (+) +char (10) + ' percent of the function: Modify record in table ' [email  protected]+char (+replicate) (' * ') +char (10) + ' percent percent write: ' [Email protected]+char] +char (10) + Replicate (' * ') + '/',--selectwhere@selectwhere0= ' if object_id (' +quotename (' R ' [email protected]+ ' by ' + Stuff (Left (@Identity, CHARINDEX (char), @Identity)-1), "(+),"), "") + ', ' P ') are NOT null ' +char (+char (9) + ' DROP PROC ' +quotename (' R ' [email protected]+ ' by ' +stuff (left (@Identity, charindex (char (+), @Identity)-1), 1, 1, ')) + char (+) + ' Go ' +char (+char) + '/' +replicate (' * ') +char (10) + ' percent stored procedure name: R ' [email protected]+ ' by ' +stuff ( Left (@Identity, chArindex (char (1), @Identity)-), +char (+) +char (+) +n ' percent input parameter: ' [Email protected]+char] +char (+n) Percent output parameter: ' +char (Ten) +char (ten) +n ' percent of the function: read the records according to the conditions in the table ' [Email protected]+char (Ten) +replicate (' * ') +char (10) + ' percent Written: ' [Email protected]+char (+char] +replicate (' * ') + '/',--SELECT @SELECT0 = ' If object_id (' +quotename ( ' R ' [email protected]+ ' ForAll ', ' ') + ', ' P ') is not NULL ' +char (IN) +char (9) + ' DROP PROC ' +quotename (' r ' [email  protected]+ ' ForAll ') +char (+) + ' Go ' +char (+char) + '/' +replicate (' * ') +char (10) + '% stored procedure name: R ' [email  protected]+ ' ForAll ' +char (+) +char (10) + ' percent of input parameters: ' +char (10) +char (10) + '% output parameter: ' +char (Ten) +char ' + ' + '% Function: Read record in table ' [Email protected]+char (+replicate] (' * '), +char (10) + ' percent of percent written: ' [Email protected]+char (10) + char (+replicate) + '/', @DELETE = ' If object_id (' +quotename (' d ' [email protected], ' ") + ', ' P ') is not Null ' +char (+char) (9) + ' DROP PROC ' +quotename (' d ' [email protected]) +char (Ten) + ' Go ' +char (+) +char (10) + '/' + Replicate (' * ', 1+char (10) + '% of stored procedure name: d ' [Email protected]+char (Ten) +char (10) + ' percent of input parameters: ' +char (10) +char ' + '% output parameter: ' [email  protected]+char (+char) (10) + ' percent of weight function: Delete record in table ' [Email protected]+char (Ten) +replicate (' * ') +char (10) + '% % written: ' [Email protected]+char (+char] +replicate (' * ') + '/' +char (+) [Email protected]+char (13) + char (+) + ' Go ' +char (+char) print ' use ' +db_name () +char (+char) + ' Go ' +char (+char) Print @Insert0print @Insert1print @Insert2print @Insert3PRINT ' Go ' +char (+char) print @UPDATE0print @UPDATE1print @UPDATE2print @ Update3print ' Go ' +char (+char) print @SELECTWHERE0print @SELECTWHERE1PRINT ' Go ' +char (+char) print @ Select0print @SELECT1PRINT ' Go ' +char (+char) print @DELETEgoexec sp_ms_marksystemobject ' curdsqlstring '-- Add in system stored procedure directory go

　　

2. CREATE TABLE TAB1

e.g.

 Use [Test]GO/** * * * object:table [dbo].    [TAB1] Script DATE:2016/5/6 11:51:47 * * * * **/IF object_id('TAB1','U') is  not NULLDROP TABLE [dbo].[TAB1]GOCREATE TABLE [dbo].[TAB1](    [ID] [BIGINT] NULL,    [Name] [sysname]  not NULL)  on [PRIMARY]GO

3. Call Method:

--Call method: Exec curdsqlstring ' Tab1 '--table name

Display effect:

Use Testgoif object_id (' cTab1 ', ' P ') are not nulldrop PROC [ctab1]go/************************************************** **************************************************************************************************************% % stored Procedure name: ctab1%% input parameter: @ID, @Name percent output parameter: percent function: new record in table tab1************************************************************   Percent write: Roy 2016-05-06***************************************************************************************************** /create PROCEDURE cTab1 (@ID bigint, @Name sysname) Asinsert into dbo. TAB1 ([Id],[name]) VALUES (@ID, @Name) goif object_id (' uTab1 ', ' P ') are not nulldrop PROC [utab1]go/*********************** *************************************************************************************************************** Percent stored procedure name: utab1%% input parameter: @ID, @Name percent output parameter: Percent function: Modify record in table tab1************************************************************************************************************************* Percent write: Roy 2016-05-06*****************************************************  /create PROCEDURE uTab1 (@ID bigint, @Name sysname) asupdate dbo. Tab1set [Name][email protected]where [Id][email protected]goif object_id (' RTab1ByID ', ' P ') is not NullDROP PROC [rtab1byid]go/******************************************************************************************** Percent stored procedure name: rtab1byid%% input parameter: @ID bigint%% output parameter: percent Function: Read the record according to the condition in the table tab1********************************************************************************************** Percent write: Roy 2016-05-06************************** *********************************************/create PROCEDURE Rtab1byid (@ID bigint) ASSET NOCOUNT on; SET TRANSACTION Isolation level READ uncommitted; SELECT [Id],[name]fromdbo.tab1where[id][email protected]goif object_id (' Rtab1forall ', ' P ') is not NullDROP PROC [ rtab1forall]go/*********************************************************************************************** Percent stored procedure name: rtab1forall%% input parameter: percent output parameter:% Function: Read record in table tab1************************************************************************************************** Percent write: Roy 2016-05-06****************************** *************************************************************************************************************** /create PROCEDURE Rtab1forallasset NOCOUNT on; SET TRANSACTION Isolation level READ uncommitted; SELECT [ID],[NAME]FROMDBO.TAB1GOif object_id (' dTab1 ', ' P ') is not nulldrop PROC [dtab1]go/********************************************************* Percent stored procedure name: DTAB1 Percent input parameter: percent output parameter: @ID bigint%% function: Delete record in table tab1************************************************************************ Percent write: Roy 2016-05-06**** *************************************************************************************************************** /create PROCEDURE dTab1 (@ID bigint) asdelete dbo. TAB1 WHERE [Id][email protected]go

　　

SQL Server Development Interface generation method