将在点网上定义的SQL脚本转换为存储过程/TSQL

Question

我正在将内联SQL转换为存储过程。以下是内联脚本。

cmdText += "insert into scopeofworktoapptresultsoverride(scopeofworkid, overwrittentext, createdbyid, datecreated, alptorprodid, isaccomplishlist, isproducttype, ismaterial, apptresultid) "
cmdText += "values (" & iNewOverrideId & ", '" & sText & "',  " & iEmployeeId & ", getdate(), " & iTiedToId & ", "
If sTiedTo = "material" Then
    cmdText += "0, 0, 1 "
ElseIf sTiedTo = "producttype" Then
    cmdText += "0, 1, 0 "
Else
    cmdText += "1, 0, 0 "
End If
cmdText += ", " & iResult & " )"

我已经转换了这个内联SQL，如下所示。是否有其他方法可以在带有case…的单个插入语句中实现此功能。。。什么时候或者一些最好的方式。。。。。

IF @sTiedTo = 'material'
    BEGIN
        INSERT INTO scopeofworktoapptresultsoverride (
            scopeofworkid
            ,overwrittentext
            ,createdbyid
            ,datecreated
            ,alptorprodid
            ,isaccomplishlist
            ,isproducttype
            ,ismaterial
            ,apptresultid
            )
        VALUES (
            @iNewOverrideId
            ,@sText
            ,@iEmployeeId
            ,getdate()
            ,@iTiedToId
            ,0
            ,0
            ,1
            ,@iResult
            )
    END
    ELSE IF @sTiedTo = 'producttype'
    BEGIN
        INSERT INTO scopeofworktoapptresultsoverride (
            scopeofworkid
            ,overwrittentext
            ,createdbyid
            ,datecreated
            ,alptorprodid
            ,isaccomplishlist
            ,isproducttype
            ,ismaterial
            ,apptresultid
            )
        VALUES (
            @iNewOverrideId
            ,@sText
            ,@iEmployeeId
            ,getdate()
            ,@iTiedToId
            ,0
            ,1
            ,0
            ,@iResult
            )
    END
    ELSE
    BEGIN
        INSERT INTO scopeofworktoapptresultsoverride (
            scopeofworkid
            ,overwrittentext
            ,createdbyid
            ,datecreated
            ,alptorprodid
            ,isaccomplishlist
            ,isproducttype
            ,ismaterial
            ,apptresultid
            )
        VALUES (
            @iNewOverrideId
            ,@sText
            ,@iEmployeeId
            ,getdate()
            ,@iTiedToId
            ,1
            ,0
            ,0
            ,@iResult
            )
    END

Answer 1

类似的东西

INSERT INTO scopeofworktoapptresultsoverride (
        scopeofworkid
        ,overwrittentext
        ,createdbyid
        ,datecreated
        ,alptorprodid
        ,isaccomplishlist
        ,isproducttype
        ,ismaterial
        ,apptresultid
        )
    VALUES (
        @iNewOverrideId
        ,@sText
        ,@iEmployeeId
        ,getdate()
        ,@iTiedToId
        ,CASE WHEN @sTiedTo <> 'material' AND @isTiedTo <> 'producttype' THEN 1 ELSE 0 END
        ,CASE WHEN @sTiedTo = 'producttype' THEN 1 ELSE 0 END
        ,CASE WHEN @sTiedTo = 'material' THEN 1 ELSE 0 END
        ,@iResult
        )

Answer 2

Declare @isaccomplishlist bit
Declare @isproducttype bit
Declare @ismaterial bit
Set @isaccomplishlist = 0
Set @isproducttype = 0
Set @ismaterial = 0
IF @sTiedTo = 'material' BEGIN    
    Set @ismaterial = 1    
END
ELSE IF @sTiedTo = 'producttype' BEGIN
    Set @isproducttype = 1
END
ELSE BEGIN
    Set @isaccomplishlist = 1
END
INSERT INTO scopeofworktoapptresultsoverride (
            scopeofworkid
            ,overwrittentext
            ,createdbyid
            ,datecreated
            ,alptorprodid
            ,isaccomplishlist
            ,isproducttype
            ,ismaterial
            ,apptresultid
            )
        VALUES (
            @iNewOverrideId
            ,@sText
            ,@iEmployeeId
            ,getdate()
            ,@iTiedToId
            ,@isaccomplishlist
            ,@isproducttype
            ,@ismaterial
            ,@iResult
            )

Answer 3

您可以为每个布尔字段使用CASE，这样您只需要一个insert语句(https://msdn.microsoft.com/en-us/library/ms181765.aspx)例如设置IsMaterial列使用：当@sTiedTo="material"时，则1 else 0结束