sqlserver ,通过表名生成update和insert语句！

CREATE PROCEDURE [dbo].[pro_savedata]

    @TABLENAME VARCHAR(50),/*ログインユーザコード*/
    @SAVEFLG INT ,/*　画面のTAG値　*/
    @RETURNSQL VARCHAR(7000) ='0' OUTPUT
AS
 DECLARE @NAME VARCHAR(60)
 DECLARE @KEYNAME VARCHAR(60)             
    DECLARE @SQLHEADER VARCHAR(60)
    DECLARE @SQLBUILD VARCHAR(3000)
    DECLARE @SQLWHEREHEADER VARCHAR(60)
    DECLARE @SQLWHERE VARCHAR(3000)
    DECLARE @SQLHEADER_INSERTUP VARCHAR(2000)
    DECLARE @SQLHEADER_INSERTDOWN VARCHAR(2000)

DECLARE CURGETCOLUMN CURSOR FAST_FORWARD
 FOR 
SELECT NAME, ISNULL(KENT.KEYNAME, 0) AS KEYNAME
  FROM SYSCOLUMNS
  LEFT JOIN (SELECT SYSCOLUMNS.NAME KEYNAME
               FROM SYSCOLUMNS, SYSOBJECTS
              WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
                AND SYSOBJECTS.NAME = @TABLENAME
                AND SYSCOLUMNS.COLID IN
                    (SELECT COLID
                       FROM SYSOBJECTS, SYSINDEXKEYS
                      WHERE SYSINDEXKEYS.ID = SYSOBJECTS.ID
                        AND SYSOBJECTS.NAME = @TABLENAME)) AS KENT ON NAME =
                                                                            KENT.KEYNAME
 WHERE ID = OBJECT_ID(@TABLENAME)

IF @SAVEFLG = '1'
BEGIN
 SET @SQLHEADER='UPDATE '+@TABLENAME + ' SET '
 SET @SQLWHEREHEADER = ' WHERE '
 SET @SQLBUILD = ''
 SET @SQLWHERE = ''
 OPEN CURGETCOLUMN
 FETCH CURGETCOLUMN INTO   @NAME, --COLNAME
     @KEYNAME   --COLNAMEKEY

 WHILE @@FETCH_STATUS = 0
 BEGIN          
  IF LTRIM(RTRIM(@KEYNAME)) <> '0'
   BEGIN 
      
    SET  @SQLWHERE =@SQLWHERE+ @KEYNAME + '= @' + @KEYNAME +' AND '
   END
  ELSE
            BEGIN
                IF NOT ( @NAME= 'INS_EMP_CD' OR @NAME= 'INS_DATE')
     SET @SQLBUILD = @SQLBUILD+ @NAME + ' = @' + @NAME+','
   END
    FETCH CURGETCOLUMN INTO   @NAME,    --COLNAME
                  @KEYNAME     
 END
    CLOSE CURGETCOLUMN
    DEALLOCATE CURGETCOLUMN

 SET @SQLWHERE = LEFT(@SQLWHERE,LEN(@SQLWHERE)-4)
 SET @RETURNSQL= @SQLHEADER + @SQLBUILD+@SQLWHEREHEADER + @SQLWHERE
    SET @RETURNSQL = REPLACE(@RETURNSQL,', WHERE ',' WHERE ')

 RETURN 
END

IF @SAVEFLG = '0' 
BEGIN
 SET @SQLHEADER='INSERT INTO '+@TABLENAME + '('
 SET @SQLHEADER_INSERTUP = ''
 SET @SQLHEADER_INSERTdown = ''
 OPEN CURGETCOLUMN
 FETCH CURGETCOLUMN INTO   @NAME, --COLNAME
     @KEYNAME   --COLNAMEKEY
 WHILE @@FETCH_STATUS = 0
 BEGIN    
       SET @SQLHEADER_INSERTUP = @SQLHEADER_INSERTUP + @NAME+','
       SET @SQLHEADER_INSERTDOWN = @SQLHEADER_INSERTDOWN +'@'+ @NAME+','  
  
    FETCH CURGETCOLUMN INTO   @NAME,    --COLNAME
     @KEYNAME     
 END
 CLOSE CURGETCOLUMN
 DEALLOCATE CURGETCOLUMN 
   
 SET @RETURNSQL= @SQLHEADER + RTRIM(@SQLHEADER_INSERTUP)+')VALUES ('+RTRIM(@SQLHEADER_INSERTDOWN) +')' 
    SET @RETURNSQL = REPLACE(@RETURNSQL,',)',')')
 
 RETURN 

END

 

 

调用方法

Private Function ExcuteUpdateDB(ByRef objData As Utility.DbAccess, _
                                ByVal imgdata() As Byte, ByVal strTime As String) As Boolean

        Dim strOut As String                        'チェック戻る値
        Dim sqlparamsUpdate(13) As SqlParameter            '引数

        Try

            '更新のSQL文取り
            strOut = Fn_GetDBSql(objData, "employee", 1)

 

            sqlparamsUpdate(0) = New SqlParameter("@empcd", SqlDbType.Char, 4) '担当者コード
            sqlparamsUpdate(1) = New SqlParameter("@empname", SqlDbType.VarChar, 20) '担当者名
              

 

 

            sqlparamsUpdate(0).Value = getConvertNull(getEscape(txtCode.Text.Trim))
            sqlparamsUpdate(1).Value = getConvertNull(getEscape(txtMei.Text.Trim))

            objData.ExcuteSpNonQueryImage(strOut, sqlparamsUpdate)

 

            Return True

        Catch ex As Exception

            Throw
        End Try

    End Function

 

 

调用方法2

    Public Shared Function Fn_GetDBSql(ByRef objData As Utility.DbAccess, _
                                                             ByVal strTableName As String, _
                                                             ByVal intFlg As Integer) As String
        Dim sqlProcedureName As String              'システムストアドプロシージ
        Dim sqlparams(2) As SqlParameter            '引数
        Dim strOut As String

        Try
            'ストアドプロシージ名をセットする。
            sqlProcedureName = "PRO_SAVEDATA"

            sqlparams(0) = New SqlParameter("@TableName", SqlDbType.VarChar, 50)        'テーブル名
            sqlparams(1) = New SqlParameter("@SaveFlg", SqlDbType.Int)
            sqlparams(2) = New SqlParameter("@ReturnSql", SqlDbType.VarChar, 4000)      'SQL文

            sqlparams(0).Direction = ParameterDirection.Input
            sqlparams(1).Direction = ParameterDirection.Input
            sqlparams(2).Direction = ParameterDirection.Output

            sqlparams(0).Value = strTableName
            sqlparams(1).Value = intFlg
            sqlparams(2).Value = "0"

            objData.ExcuteSpNonQuery(sqlProcedureName, sqlparams)

            '戻りSQL文
            strOut = sqlparams(2).Value

            Return strOut.Trim

        Catch ex As Exception

            Throw Utility.KTException.KTException(ex, enuExceptionType.DBException)
        End Try

    End Function