CODEWITHSUNDEEP
sql-serversql-server-2012sql-server-2008-r2
StackUser
StackUser

Reputation: 5398

Convert SQL script defined at dot net to stored procedure / TSQL

I am in the process of converting inline SQL to Stored Procedure. The following is the inline Script.

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 & " )"

I have already converted this inline SQL like below. Is anyother way to implement this in a single insert statement with case...when.... or some best way.....

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

Upvotes: 0

Views: 37

Answers (3)

Liesel
Liesel

Reputation: 2979

Something like (untested, off the top of my head)

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
        )

Upvotes: 2

Gavin
Gavin

Reputation: 516

You can look to use CASE for each of the boolean fields so you only need one insert statement (https://msdn.microsoft.com/en-us/library/ms181765.aspx) for example to set the IsMaterial column use : case when @sTiedTo = 'material' then 1 else 0 end

Upvotes: 0

shadow
shadow

Reputation: 1903

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
            )

Upvotes: 1

Related Questions