we are having issues with a stored procedure. When calling it from our website via asp/vb.net it seems to not be executing properly. If I run it from SSMS it works.
I have run the debugger when the call is being placed, the parameters being passed in are correct at the time of the ExecuteNonQuery() call but it is not generating any records in the related tables like it should. If I use the same values seen while debugging our website directly in SSMS, the stored procedure creates the expected records.
Here is our stored procedure:
ALTER PROCEDURE [dbo].[CopyGoals](
@OldVisitID int,
@NewVisitID int,
@CreatedBy NVarChar(30)
) AS BEGIN
declare @GoalMapping As Table(OldGoalID int,NewGoalID int);
Merge Into VisitGoals
Using(
select GoalsID,Goal,ProgressNote,Progress,Completed,CreatedOn,CreatedBy,VisitID
From VisitGoals
Where VisitID = @OldVisitID
) As Src
On 1 = 0
When Not Matched By Target Then
Insert (Goal,ProgressNote,Completed,VisitID,Progress, CreatedOn, CreatedBy)
Values (Src.Goal, Src.ProgressNote, Src.Completed, @NewVisitID, Src.Progress, GetDate(), @CreatedBy)
Output Src.GoalsID As OldGoalID, inserted.GoalsID as NewGoalID
Into @GoalMapping;
Insert Into SubGoals(GoalID,VisitID,GoalText,HasCompleted,WillComplete,GoalStatus)
(
Select GM.NewGoalID, @NewVisitID, SG.GoalText, SG.HasCompleted, SG.WillComplete, SG.GoalStatus
From SubGoals As SG inner join @GoalMapping As GM on SG.GoalID = GM.OldGoalID
Where SG.VisitID = @OldVisitID
)
END
Here is the procedure call from our website page:
Dim conStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim curUsr As New Supervisor(Context.User.Identity.Name, True)
Using con As New SqlConnection(conStr)
Using cmd As New SqlCommand("CopyGoals", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@OldVisitID", SqlDbType.Int).Value = oldVID
cmd.Parameters.Add("@NewVisitID", SqlDbType.Int).Value = newVID
cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 30).Value = curUsr.Name
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
What this procedure should do, and does if run from SSMS, is look at our Goals table with the existing IDs matching a foreign key corresponding to the VisitID in a different table to find all goals associated with that old visit.
It them copies the goal information and inserts it into the Goals table, outputting the old goal IDs and the newly inserted goal IDs into the @GoalMapping table.
It then looks into our SubGoals table and does a similar thing by copying each subgoal related to the goals we just copied. For whatever reason, this procedure does not execute properly when called from our page even when it runs in SSMS when we feed it the same input values as seen during debugging of the page. There are no errors reported in chrome's debugger, we tried wrapping execute in a try with an SQL and normal exception but neither of them tripped. We are pretty stumped. Maybe someone will spot something we haven't over the last few days.
Here is the output from Sql Profiler:
