For the improvement of the query, it is not limited to the input parameter returns, for example, if all the income parameters in a stored procedure are not filled, the entire result is returned by default.
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [PR__sys_makeselectrecTrecordproc] ") And ObjectProperty (id, n'isprocedure') = 1)
Drop Procedure [DBO]. [PR__sys_makeselectrtructure "
Go
CREATE PR__SYS_MAKESELECTRECORDPROC
@stablename varchar (128),
@Bexecute bit = 0
AS
If DBO.FNTABLEHASPRIMARYKEY (@Stablename) = 0
Begin
Raiserror ('Procedure Cannot Be CREATED ON A Table with no primary key.', 10, 1)
Return
End
Declare @sproctext varchar (8000),
@skeyfields varchar (2000),
---------------------------------------------
@sallfields varchar (2000),
@sallparams varchar (2000),
@Biswhere Bit,
---------------------------------------------
@SselectcLause Varchar (2000),
@Swhereclause Varchar (2000),
@scolumnname varchar (128),
@ncolumnid smallint,
@BPrimaryKeyColumn bit,
@nalternateType Int,
@ncolumnLength Int,
@ncolumnPrecision Int,
@ncolumnscale int,
@Isnullable bit,
@IsIndentity Int,
@stypename varchar (128),
@SDefaultValue Varchar (4000),
@scrlf char (2),
@stab char (1)
Set @stab = char (9)
Set @scrlf = char (13) char (10)
Set @sproctext = ''
Set @skeyfields = ''
Set @ssselectclause = ''
Set @SwhereClause = ''
Set @sallfields = ''
Set @sallparams = ''
Set @biswhere = 0
SET @sProcText = @sProcText 'IF EXISTS (SELECT * FROM sysobjects WHERE name =' 'up_' @sTableName '_Select' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC up_' @stablename '_select' @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Set @sproctext = @sproctext @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Set @sproctext = ''
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @sproctext = @SPROCTEXT '- SELECT A SINGLE RECORD FROM' @stablename @SCRLF
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @SPROCTEXT = @sprocText 'Create Proc Up_' @Stablename '_Select ' @SCRLF
Declare CRKEYFIELDS CURSOR for
SELECT *
From dbo.fntableColumnInfo (@stablename)
ORDER BY 2
Open crKeyfields
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
While (@@ fetch_status = 0)
Begin
-------------------------------------------------- -------------------------------------------------- IF (@Skeyfields <> ')
Set @skeyfields = @skeyfields ',' @SCRLF
Set @skeyfields = @skeyfields @stab '@P_' @scolumnname '' @stypename
IF (@sallfields <> ')
Begin
Set @sallparams = @sallparams ','
Set @sallfields = @sallfields ','
End
IF (@stypename = 'TimeStamp')
Set @sallparams = @sallparams 'null'
Else IF (@SDEFAULTVALUE IS NOT NULL)
Set @sallparams = @sallparams 'coalesce (@P_' @scolumnname ',' @SDEFAULTVALUE ')')
Else
Set @sallparams = @SallParams '@P_' @scolumnname
Set @sallfields = @sallfields @scolumnname
-------------------------------------------------- -------------------------------------------------
IF (@NALTERNATETYPE = 2) - Decimal, Numeric
Set @skeyfields = @skeyfields '(' CAST (@ncolumnprecision as varchar (3)) ','
CAST (@ncolumnscale as varchar (3)) ')'
Else if (@nalternatetype = 1) - Character and Binary
Set @skeyfields = @skeyfields '(' CAST (@ncolumnlength as varchar (4)) ')'
/ *
IF (@SDEFAULTVALUE IS NOT NULL) or (@SNULLABLE = 1) or (@stypename = 'TimeStamp')
Set @skeyfields = @skeyfields '= null'
Else if (@isnullable = 0) set @skeyfields = @SKEYFIELDS '= NULL'
- set @skeyfields = @Skeyfields '= 0'
* /
IF (@stypename = 'char') or (@stypename = 'varchar ") or (@stypename =' nchar ')
Set @skeyfields = @skeyfields '=' char (39) char (39)
Else
Set @skeyfields = @Skeyfields '= 0'
-------------------------------------------------- -------------------------------------------------
IF (@BPRIMARYKEYCOLUMN = 1)
Begin
- IF (@SKEYFIELDS <> ')
- set @skeyfields = @skeyfields ',' @SCRLF
- set @skeyfields = @Skeyfields @stab '@P_' @scolumnname '' @stypename
- IF (@nalternateType = 2) - Decimal, Numeric
- set @skeyfields = @skeyfields '(' Cast (@ncolumnprecision as varchar (3)) ','
- CAST (@ncolumnscale as varchar (3)) ')'
- else if (@nalternatetype = 1) - Character and binary
- set @skeyfields = @skeyfields '(' Cast (@ncolumnlength as varchar (4)) ')'
IF (@swhereclause = '')
Begin
Set @Swhereclause = @SwhereClause 'Where'
Set @biswhere = 1
End
Else
Begin
- set @Swhereclause = @Swhereclause 'and'
IF (@Biswhere <> 1) Begin
IF (@stypename = 'char') or (@stypename = 'varchar ") or (@stypename =' nchar ')
Set @Swhereclause = @Swhereclause @stab '(@P_' @scolumnname '=' CHAR (39) CHAR (39) 'OR' @ Scolumnname '= @P_' @scolumnname ' ) ' @SCRLF
Else
Set @Swhereclause = @SwhereClause @stab '(@P_' @scolumnname '= 0 or' @ Scolumnname '= @P_' @scolumnname ')' @scrlf
End
- set @Swhereclause = @Swhereclause @stab '(@P_' @scolumnname '= null or' @ Scolumnname '= @P_' @scolumnname ')') ' @scrlf
End
- set @Swhereclause = @Swhereclause @stab @scolumnname '= @P_' @scolumnname @SCRLF
End
IF (@Swhereclause <> ')
Begin
IF @biswhere = 0
Set @SwhereClause = @SwherecLaSe 'and'
IF (@stypename = 'char') or (@stypename = 'varchar ") or (@stypename =' nchar ')
Set @Swhereclause = @Swhereclause @stab '(@P_' @scolumnname '=' CHAR (39) CHAR (39) 'OR' @ Scolumnname '= @P_' @scolumnname ' ) ' @SCRLF
Else
Set @Swhereclause = @SwhereClause @stab '(@P_' @scolumnname '= 0 or' @ Scolumnname '= @P_' @scolumnname ')' @scrlf
- set @Swhereclause = @Swhereclause @stab '(@P_' @scolumnname '= null or' @ Scolumnname '= @P_' @scolumnname ')') ' @scrlf
Set @biswhere = 0
End
IF (@SselectcLause = ')
Set @ssselectclause = @SselectcLaSe 'SELECT'
Else
Set @ssselectclause = @SselectcLaSe ',' @scrlf
Set @ssselectclause = @SselectcLaSe @stab @scolumnname
-------------------------------------------------- -------------------------------------------------
/ *
IF (@isiDentity = 0)
Begin
IF (@SDEFAULTVALUE IS NOT NULL) or (@SNULLABLE = 1) or (@stypename = 'TimeStamp')
Set @skeyfields = @skeyfields '= null'
End
* /
-------------------------------------------------- -------------------------------------------------
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
End
Close Crkeyfields
Deallocate CRKEYFIELDS
Set @ssselectclause = @SselectcLaSe @scrlf
Set @sproctext = @sproctext @SKEYFIELDS @SCRLF
Set @sproctext = @sproctext 'as' @scrlf
Set @sproctext = @sproctext @scrlf
Set @sproctext = @SPROCTEXT @SselectcLause
Set @sproctext = @sproctext 'from' @stablename @scrlf
Set @sproctext = @sprocText @SwhereClause
Set @sproctext = @sproctext @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Below is a table for testing:
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [t1]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [T1] Go
Create Table [DBO]. [T1] ([Id] [INT] NOT NULL, [NAME] [CHAR] (10) Collate Chinese_PRC_CI_AS NULL, [E_TIME] [DATIME] NULL, [V] [Decimal] (18, 0 NULL, [F] [FLOAT] NULL, [C] [INT] NULL, [AAF] [Numeric] (18, 0) NULL, [FDSAS] [VARCHAR] (50) Collate Chinese_PRC_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [Primary] Go
The generated stored procedures are as follows (note: there are fewer GOs generated automatically, but this can be generated directly to SQL Server, without affecting operation):
IF exists (SELECT * from sysobjects where name = 'up_t1_select') Drop Proc Up_t1_select
-------------------------------------------------- ---------------------------- SELECT A SINGLE RECORD from T1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------- Create Proc UP_T1_SELECT @P_ID INT = 0, @P_Name Char (10) = ', @P_e_time DateTime = 0, @P_V Decimal (18, 0) = 0, @P_f float = 0, @P_c INT = 0, @P_AAF Numeric (18, 0) = 0, @P_fdsas varchar (50) = 'asselect ID, Name, E_TIME, V, F, C, AAF, FDSASFROM T1where (@P_ID = 0 OR ID = @P_ID) and (@P_NAME = 'or Name = @P_NAME) AND (@P_E_TIME = 0 or E_TIME = @P_E_TIME) AND (@P_V = 0 or v = @P_V) and (@P_f = 0 OR) f = @P_F) AND (@P_C = 0 or C = @P_C) and (@P_AAF = 0 or AAF = @P_AAF) and (@P_FDSAS = '' OR fdsas = @P_fdsas)
