Utilizing stored procedures

[StevenK]

I'm working with C# code against a SQL Server database backend. I'm used to using SQL syntax to generate a result set and then interrogate the generated result set (albeit that my experience is from a different language - Delphi).

I'm now looking at making use of stored procedures in the target SQL Server database but need to be sure that the running of these is waited upon. By this I mean that if I make use of a stored procedure in the 2nd step (block B) of a 3-step code block (run A, then run B, then run C) that code block C is not run before the stored procedure has performed it's workload (be it that it a value returned or a result set returned).
Can I be sure that the stored procedure is completed within step B before step C is started to be run by the application code ?

Is this the case ?

I hope that this makes sense ?

Steve

 

[RiverGuy]

I'm not 100% sure about that...but one way to check would be to make a temporary table with one field. Add one insert statement to each stored procedure like at the end of procedure #1:

INSERT INTO TableTemp(Field1) VALUES(1)


end of #2:

INSERT INTO TableTemp(Field1) VALUES(2)

end of #3:

INSERT INTO TableTemp(Field1) VALUES(3)

before you do procedure #2, check to see if the value in that field is 1. If its not, then the procedure isn't done.

 

[StevenK]

I'm guessing that in this simple proposed example it would work OK.
My concern would be that if the stored procedure in the first code block had a heavy amount of processing to do, would it complete before the running of a small simple stored procedure in my second code block.

Does anyone have an opinion on this ?

Steve

 

[spas]

Based upon my experience, the execution of the stored procedure (or SQL statement) is a blocking call; therefore, your code will wait for its completion.

An effective way to test this would be to create a stored procedure with a parameter--to identify the instance-- and then execute it in your two different code blocks

This stored procedure would need to be time-consuming like the following example:

CREATE TABLE TEST (
  ID VARCHAR(100),
  CNT INT,
  TIMESTMP DATETIME
)

Create Procedure "TESTME"
(
	@ID VARCHAR(100)
)
As
	SET NOCOUNT ON
	DECLARE @I INTEGER

	SET @I = 0
	WHILE @I < 10000
	BEGIN

		SELECT * FROM <a big table>

		SET @I = @I + 1
		IF @I % 100 = 0 
			INSERT INTO TEST (ID,CNT, TIMESTMP)
				VALUES (@ID,@I,GETDATE())
	END
	SELECT * FROM TEST
	return

You could then run the stored procedure TESTME with
@ID="Test1" in one code block and @ID="Test2" in the second code block.

Finally, a SELECT * FROM TEST ORDER BY TIMESTMP would prove when the code executed.

Hope this Helps,
Scott

 

[xloop]

Yes it will definitely wait.

If it's returning a result (and even if it isn't) to your application how can it *not* wait?!