Transact SQL :: Executing Stored Procedures And Threading
Jul 10, 2015
If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?
The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems.
If I group all stroed procs in one that simply calls all of them in the needed sequence:
exec stproc1
exec stproc2
.....
exec stprocn
Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?
View 2 Replies
Nov 2, 2006
I have the following sp..REATE Procedure SSSP_VehicleReg_Add @DECAL_NO varchar (9) , @NAME varchar (26) , @SSN varchar (9) , @DLN varchar (10) , @Address_1 varchar , @Address_2 varchar , @City varchar (50) , @State_Code varchar , @Zip_Code varchar (9) , @Country_Code varchar (4) , @PHONE varchar (12) , @VM varchar (10) , @MODEL varchar (10) , @VLPNUMBER varchar (10) , @VLPSTATE varchar (2) , @Date_Iss datetime, @Date_Exp datetime, @tran_Date datetime, @Op_Code varchar (20) , @ReturnStatus integer output, @ReturnMessage Varchar (50) output /* Adds a record to the Vehicle Registration table.*/ AS If @Decal_no = ' ' Begin Set @ReturnStatus = 0 Set @ReturnMessage = 'Record not added Key Fields are not valid' End Else If dbo.VehicleReg_Check (@Decal_No) = 0 Begin Insert INTO VehicleReg (Decal_NO, Name, SSN, DLN, Address_1, Address_2, City, State_Code, Zip_Code, Country_Code, Phone, VM, Model, VLPNUMBER, VLPSTATE, Date_Iss, Date_Exp, Tran_Date, Op_Code) Values (@Decal_NO, @Name, @SSN, @DLN, @Address_1, @Address_2, @City, @State_Code, @Zip_Code, @Country_Code, @Phone, @VM, @Model, @VLPNUMBER, @VLPSTATE, @Date_Iss, @Date_Exp, @Tran_Date, @Op_Code) Set @ReturnStatus = 1 Set @ReturnMessage = 'Record added' End Else Begin Set @ReturnStatus = 0 Set @ReturnMessage = 'Record not added' EndGO That is being called via...Dim SqlCmd As New SqlCommand("SSSP_VehicleReg_ADD", MYGF.cnMydb)Dim Current_Time As String = CStr(Now.Date + Now.TimeOfDay)SqlCmd.CommandType = CommandType.StoredProcedureSqlCmd.Parameters.Add("@Decal_No", SqlDbType.VarChar, 9, txtDecal.Text)SqlCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 26, txtName.Text)SqlCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9, txtStuid.Text)SqlCmd.Parameters.Add("@DLN", SqlDbType.VarChar, 10, txtDln.Text)SqlCmd.Parameters.Add("@Address_1", SqlDbType.VarChar, 50, txtAddress_1.Text)SqlCmd.Parameters.Add("@Address_2", SqlDbType.VarChar, 50, txtAddress_2.Text)SqlCmd.Parameters.Add("@City", SqlDbType.VarChar, 50, txtCity.Text)SqlCmd.Parameters.Add("@State_Code", SqlDbType.VarChar, 2, ddlState.Text)SqlCmd.Parameters.Add("@Zip_Code", SqlDbType.VarChar, 9, txtZip.Text)SqlCmd.Parameters.Add("@Country_Code", SqlDbType.VarChar, 4, " ")SqlCmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 12, txtPhone.Text)SqlCmd.Parameters.Add("@VM", SqlDbType.VarChar, 10, txtMake.Text)SqlCmd.Parameters.Add("@MODEL", SqlDbType.VarChar, 10, txtModel.Text)SqlCmd.Parameters.Add("@VLPNUMBER", SqlDbType.VarChar, 10, txtTagNo.Text)SqlCmd.Parameters.Add("@VLPSTATE", SqlDbType.VarChar, 2, " ")SqlCmd.Parameters.Add("@Date_Iss", SqlDbType.DateTime, 8, txtIssDate.Text)SqlCmd.Parameters.Add("@Date_Exp", SqlDbType.DateTime, 8, txtExpDate.Text)SqlCmd.Parameters.Add("@tran_Date", SqlDbType.DateTime, 8, Current_Time)SqlCmd.Parameters.Add("@Op_Code", SqlDbType.VarChar, 20, "xxxx")Dim ReturnStatus As New SqlParameter(("@ReturnStatus"), SqlDbType.Int, 1, ParameterDirection.Output)Dim ReturnMessage As New SqlParameter(("@ReturnMessage"), SqlDbType.VarChar, 50, ParameterDirection.Output)SqlCmd.Parameters.Add(ReturnStatus)SqlCmd.Parameters.Add(ReturnMessage)SqlCmd.ExecuteNonQuery()Which fails with the following error...System.Data.SqlClient.SqlException was unhandled by user code Class=16 ErrorCode=-2146232060 LineNumber=0 Message="Procedure 'SSSP_VehicleReg_Add' expects parameter '@DECAL_NO', which was not supplied." Number=201 Procedure="SSSP_VehicleReg_Add" Server= Source=".Net SqlClient Data Provider" State=6 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Vechicle_Registration.Move_to_database() in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 304 at Vechicle_Registration.BtnSave_Click(Object sender, EventArgs e) in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 34 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Any help would be appreciated
View 2 Replies
View Related
Jul 3, 2007
I have a table with a list of stored procedures, I iterate using a Foreach loop which i iterate through an ADO.NET recordset that I created by executing a select SQL statement.
Now my problem is that in the foreach loop i have an execute sql task container which basically just executes these stored procedures using the 'exec ?' statement (i grab the name of each procedure into a variable). However, error trapping seems to be very difficult. I just figured out how to get a ReturnCode from the execute statement, but apparently I have to use it outside the Execute SQL task ....prolly use an IF condition to see if its not 0....but how do I go about doing this?
Also, I want to error trap if a stored procedure doesnt execute because of some error, how could i possibly handle an error like that? I dont want the foreach iteration to stop on an error because a stored procedure wasnt called either, but currently it does stop the entire package.
Thoughts anyone?
View 4 Replies
View Related
Aug 28, 2002
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);
MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1
UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence
RETURN @sequence_id
END
View 1 Replies
View Related
Jul 18, 2007
I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.
I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.
123.sh sp_my_stored_procedure input_parm1 input_parm2
123.sh sp_different_stored_procedure input_parm1
This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.
How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.
Thanks, Jim
View 4 Replies
View Related