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

Question

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

Liesel · Accepted Answer · 2016-05-18 07:54:34Z

2

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
        )

answered May 18, 2016 at 7:54

Liesel

2,9792 gold badges14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StackUser Over a year ago

Thanks for your answer. I already selected Shadow answer however I can vote up.

shadow · Accepted Answer · 2016-05-18 07:52:18Z

1

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
            )

answered May 18, 2016 at 7:52

shadow

1,9031 gold badge17 silver badges25 bronze badges

1 Comment

StackUser Over a year ago

Thanks I appreciate your approach

Gavin · Accepted Answer · 2016-05-18 07:52:30Z

0

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

answered May 18, 2016 at 7:52

Gavin

5163 silver badges5 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related