多层开发中,经常写SQL SERVER存储过程的朋友看过来!

原创 2004年08月15日 18:37:00

 

下面是自动产生存储过程的sql 脚本,你可以在查询分析器中运行.

运行完,你会看到多了四个存储过程

pr__SYS_MakeInsertRecordProc

pr__SYS_MakeUpdateRecordProc

pr__SYS_MakeSelectRecordProc

pr__SYS_MakeDeleteRecordProc

执行方式:在查询分析器中执行

pr__SYS_MakeInsertRecordProc '表名'         --得到插入语句

pr__SYS_MakeInsertRecordProc '表名',1    --得到插入语句,并创建Insert存储过程

 

完整脚本如下:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 


CREATE      PROC pr__SYS_MakeDeleteRecordProc
 @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),
 @sWhereClause varchar(2000),
 @sColumnName varchar(128),
 @nColumnID smallint,
 @bPrimaryKeyColumn bit,
 @nAlternateType int,
 @nColumnLength int,
 @nColumnPrecision int,
 @nColumnScale int,
 @IsNullable bit,
 @IsIdentity 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 @sWhereClause = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''prApp_' + @sTableName + '_Delete'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC prApp_' + @sTableName + '_Delete' + @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 + '-- Delete a single record from ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + 'CREATE PROC prApp_' + @sTableName + '_Delete' + @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, @sTypeName, @sDefaultValue
    
WHILE (@@FETCH_STATUS = 0)
 BEGIN

 IF (@bPrimaryKeyColumn = 1)
  BEGIN
  IF (@sKeyFields <> '')
   SET @sKeyFields = @sKeyFields + ',' + @sCRLF
 
  SET @sKeyFields = @sKeyFields + @sTAB + '@' + @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 = '')
   SET @sWhereClause = @sWhereClause + 'WHERE '
  ELSE
   SET @sWhereClause = @sWhereClause + ' AND '

  SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName  + ' = @' + @sColumnName + @sCRLF
  END

 FETCH  NEXT
 FROM  crKeyFields
 INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
  @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
  @IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sProcText = @sProcText + @sKeyFields + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET  @sProcText = @sProcText + 'DELETE ' + @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

 

 

 

CREATE       PROC pr__SYS_MakeInsertRecordProc
 @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),
 @sWhereClause varchar(2000),
 @sColumnName varchar(128),
 @nColumnID smallint,
 @bPrimaryKeyColumn bit,
 @nAlternateType int,
 @nColumnLength int,
 @nColumnPrecision int,
 @nColumnScale int,
 @IsNullable bit,
 @IsIdentity int,
 @HasIdentity int,
 @sTypeName varchar(128),
 @sDefaultValue varchar(4000),
 @sCRLF char(2),
 @sTAB char(1)

SET  @HasIdentity = 0
SET @sTAB = char(9)
SET  @sCRLF = char(13) + char(10)
SET  @sProcText = ''
SET  @sKeyFields = ''
SET @sAllFields = ''
SET @sWhereClause = ''
SET @sAllParams  = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''prApp_' + @sTableName + '_Insert'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC prApp_' + @sTableName + '_Insert' + @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 + '-- Insert a single record into ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + 'CREATE PROC prApp_' + @sTableName + '_Insert' + @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, @sTypeName, @sDefaultValue
    
WHILE (@@FETCH_STATUS = 0)
 BEGIN
 IF (@IsIdentity = 0)
  BEGIN
  IF (@sKeyFields <> '')
   SET @sKeyFields = @sKeyFields + ',' + @sCRLF

  SET @sKeyFields = @sKeyFields + @sTAB + '@' + @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(@' + @sColumnName + ', ' + @sDefaultValue + ')'
  ELSE
   SET @sAllParams = @sAllParams + '@' + @sColumnName

  SET @sAllFields = @sAllFields + @sColumnName

  END
 ELSE
  BEGIN
  SET @HasIdentity = 1
  END

 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 (@IsIdentity = 0)
  BEGIN
  IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp')
   SET @sKeyFields = @sKeyFields + ' = NULL'
  END

 FETCH  NEXT
 FROM  crKeyFields
 INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
  @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
  @IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sProcText = @sProcText + @sKeyFields + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET  @sProcText = @sProcText + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF
SET  @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF

IF (@HasIdentity = 1)
 BEGIN
 SET  @sProcText = @sProcText + 'RETURN SCOPE_IDENTITY()' + @sCRLF
 SET  @sProcText = @sProcText + @sCRLF
 END

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 OFF
GO
SET ANSI_NULLS ON
GO

 

 

CREATE     PROC 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),
 @sSelectClause varchar(2000),
 @sWhereClause varchar(2000),
 @sColumnName varchar(128),
 @nColumnID smallint,
 @bPrimaryKeyColumn bit,
 @nAlternateType int,
 @nColumnLength int,
 @nColumnPrecision int,
 @nColumnScale int,
 @IsNullable bit,
 @IsIdentity 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 @sSelectClause = ''
SET @sWhereClause = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''prApp_' + @sTableName + '_Select'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC prApp_' + @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 prApp_' + @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, @sTypeName, @sDefaultValue
    
WHILE (@@FETCH_STATUS = 0)
 BEGIN
 IF (@bPrimaryKeyColumn = 1)
  BEGIN
  IF (@sKeyFields <> '')
   SET @sKeyFields = @sKeyFields + ',' + @sCRLF
 
  SET @sKeyFields = @sKeyFields + @sTAB + '@' + @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 = '')
   SET @sWhereClause = @sWhereClause + 'WHERE '
  ELSE
   SET @sWhereClause = @sWhereClause + ' AND '

  SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName  + ' = @' + @sColumnName + @sCRLF
  END

 IF (@sSelectClause = '')
  SET @sSelectClause = @sSelectClause + 'SELECT'
 ELSE
  SET @sSelectClause = @sSelectClause + ',' + @sCRLF

 SET @sSelectClause = @sSelectClause + @sTAB + @sColumnName

 FETCH  NEXT
 FROM  crKeyFields
 INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
  @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
  @IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sSelectClause = @sSelectClause + @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

 

 

 

 

CREATE         PROC pr__SYS_MakeUpdateRecordProc
 @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),
 @sSetClause varchar(2000),
 @sWhereClause varchar(2000),
 @sColumnName varchar(128),
 @nColumnID smallint,
 @bPrimaryKeyColumn bit,
 @nAlternateType int,
 @nColumnLength int,
 @nColumnPrecision int,
 @nColumnScale int,
 @IsNullable bit,
 @IsIdentity 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 @sSetClause = ''
SET @sWhereClause = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''prApp_' + @sTableName + '_Update'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC prApp_' + @sTableName + '_Update' + @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 + '-- Update a single record in ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + 'CREATE PROC prApp_' + @sTableName + '_Update' + @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, @sTypeName, @sDefaultValue
    
WHILE (@@FETCH_STATUS = 0)
 BEGIN
 IF (@sKeyFields <> '')
  SET @sKeyFields = @sKeyFields + ',' + @sCRLF

 SET @sKeyFields = @sKeyFields + @sTAB + '@' + @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 (@bPrimaryKeyColumn = 1)
  BEGIN
  IF (@sWhereClause = '')
   SET @sWhereClause = @sWhereClause + 'WHERE '
  ELSE
   SET @sWhereClause = @sWhereClause + ' AND '

  SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName  + ' = @' + @sColumnName + @sCRLF
  END
 ELSE
  IF (@IsIdentity = 0)
   BEGIN
   IF (@sSetClause = '')
    SET @sSetClause = @sSetClause + 'SET'
   ELSE
    SET @sSetClause = @sSetClause + ',' + @sCRLF
   SET @sSetClause = @sSetClause + @sTAB + @sColumnName  + ' = '
   IF (@sTypeName = 'timestamp')
    SET @sSetClause = @sSetClause + 'NULL'
   ELSE IF (@sDefaultValue IS NOT NULL)
    SET @sSetClause = @sSetClause + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')'
   ELSE
    SET @sSetClause = @sSetClause + '@' + @sColumnName
   END

 IF (@IsIdentity = 0)
  BEGIN
  IF (@IsNullable = 1) OR (@sTypeName = 'timestamp')
   SET @sKeyFields = @sKeyFields + ' = NULL'
  END

 FETCH  NEXT
 FROM  crKeyFields
 INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
  @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
  @IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sSetClause = @sSetClause + @sCRLF

SET  @sProcText = @sProcText + @sKeyFields + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET  @sProcText = @sProcText + 'UPDATE ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + @sSetClause
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

CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
 RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
 DECLARE @sDefaultValue varchar(4000)

 SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @sTableName
  AND  COLUMN_NAME = @sColumnName

 RETURN  @sDefaultValue

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

CREATE   FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
 DECLARE @nTableID int,
  @nIndexID int,
  @i int
 
 SET  @nTableID = OBJECT_ID(@sTableName)
 
 SELECT  @nIndexID = indid
 FROM  sysindexes
 WHERE  id = @nTableID
  AND  indid BETWEEN 1 And 254
  AND  (status & 2048) = 2048
 
 IF @nIndexID Is Null
  RETURN 0
 
 IF @nColumnName IN
  (SELECT sc.[name]
  FROM  sysindexkeys sik
   INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
  WHERE  sik.id = @nTableID
   AND  sik.indid = @nIndexID)
  BEGIN
  RETURN 1
  END


 RETURN 0
END

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

 

CREATE       FUNCTION dbo.fnTableColumnInfo(@sTableName varchar(128))
RETURNS TABLE
AS
 RETURN
 SELECT c.name AS sColumnName,
  c.colid AS nColumnID,
  dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
  CASE  WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1
   WHEN t.name IN ('decimal', 'numeric') THEN 2
   ELSE 0
  END AS nAlternateType,
  c.length AS nColumnLength,
  c.prec AS nColumnPrecision,
  c.scale AS nColumnScale,
  c.IsNullable,
  SIGN(c.status & 128) AS IsIdentity,
  t.name as sTypeName,
  dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
 FROM syscolumns c
  INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
 WHERE c.id = OBJECT_ID(@sTableName)

 

 

 


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.fnTableHasPrimaryKey(@sTableName varchar(128))
RETURNS bit
AS
BEGIN
 DECLARE @nTableID int,
  @nIndexID int
 
 SET  @nTableID = OBJECT_ID(@sTableName)
 
 SELECT  @nIndexID = indid
 FROM  sysindexes
 WHERE  id = @nTableID
  AND  indid BETWEEN 1 And 254
  AND  (status & 2048) = 2048
 
 IF @nIndexID IS NOT Null
  RETURN 1
 
 RETURN 0
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

用SQL Server编写一个存储过程

今天数据库上机要求编写一个存储过程来体会sql server的可编程性。题目如下: 数据库中有一张表 student, 有两列分别是xh varchar(10), xm varchar(50),...
  • transformer_WSZ
  • transformer_WSZ
  • 2017-06-06 01:01:28
  • 851

如何在sqlserver中写存储过程

original link http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedur...
  • w_vc_love
  • w_vc_love
  • 2013-03-09 16:53:58
  • 5084

SQL Server 存储过程中处理多个查询条件的几种常见写法分析,我们该用那种写法

本文出处: http://www.cnblogs.com/wy123/p/5958047.html    最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫,...
  • jiangfei009003
  • jiangfei009003
  • 2016-12-22 16:57:28
  • 1735

SQL中存储过程和函数的标准写法

之前一直用的存储过程,今天忽然又接触到跑算法获取返回值的问题,想到可以用函数,好久没用过一时忘了怎么写,还要现查,于是就记录下来,顺便连存储过程一块啦。 存储过程: CREATE PROC...
  • u012630420
  • u012630420
  • 2017-03-01 16:23:04
  • 3197

C#与SQL Server存储过程之一(创建):使用C#创建SQL Server的存储过程

 通常,开发人员使用的是T-SQL来创建SQL Server的存储过程、函数和触发器。 而现在的SQL Server 2005已经完全支持.NET通用语言运行时(CLR)了。 这就意味着,你可以使用....
  • hillspring
  • hillspring
  • 2008-04-18 16:29:00
  • 7714

SqlServer 存储过程 if else 两层判断 语句拼接

SqlServer 存储过程 if else 两层判断 语句拼接
  • yijiulove
  • yijiulove
  • 2011-02-24 11:54:00
  • 24925

sqlserver存储过程循环写法

用游标,和WHILE可以遍历您的查询中的每一条记录并将要求的字段传给变量进行相应的处理 ================== DECLARE @A1 VARCHAR(10), @A2 VAR...
  • u011782082
  • u011782082
  • 2013-09-02 10:02:58
  • 6381

sqlServer2000存储过程学习笔记

开始学习存储过程的编写。首先先搞清楚了几个基本的写法,从0开始,嘎嘎:声明一个变量:declare @gao varchar(30)关键字:declare变量名:@gao为啥用@,不知道,就当是必须的...
  • gaoyunpeng
  • gaoyunpeng
  • 2007-03-19 14:12:00
  • 4084

sqlserver存储过程学习(通俗易懂)

sqlserver存储过程学习(通俗易懂)
  • wanlong360599336
  • wanlong360599336
  • 2016-09-02 17:02:07
  • 15642

sql server 2008存储过程参数

--使用存储过程参数 --包括输入参数和输出参数,以及参数的默认值 --指定参数名称和数据类型 --输入参数允许用户将数据值传递到存储过程或者函数 --输出参数允许存储过程将数据值或者游标变量传递给用...
  • hephec
  • hephec
  • 2014-11-27 16:21:08
  • 1491
收藏助手
不良信息举报
您举报文章:多层开发中,经常写SQL SERVER存储过程的朋友看过来!
举报原因:
原因补充:

(最多只允许输入30个字)