SQL Server Development Interface generation method

Source: Internet
Author: User
Tags joins rtrim

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.