Stored Procedure step by step execution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure step by step execution

Author

Topic

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-11-01 : 06:33:21

Dear All,

I have such problem. If I have one stored procedure, where I want to return specific results after each batch. But the query analyzer return all results at the same time at the end of the execution. How could I solve this problem? I am using SQL Server 2000.

Example:
CREATE PROCEDURE ...
AS

INSERT ...

SELECT 'INSERT OK' AS RESULT1

UPDATE ...

SELECT 'UPDATE OK' AS RESULT2

abd etc.

Thank you in advance.

The Rebel

Edited by - vladimir_grigoro on 11/01/2002 06:34:38

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-01 : 07:43:28

Method 1:
DECLARE @RESULT1 VARCHAR (20), @RESULT2 VARCHAR (20)

SELECT @RESULT1 = 'INSERT OK'

SELECT @RESULT2 = 'UPDATE OK'
.....
SELECT @RESULT1 + @RESULT2
RETURN

Method 2:
CREATE PROCEDURE ABC
@OutputResults VARCHAR (1000) OUTPUT
AS
DECLARE @CR VARCHAR (1) -- CARRIAGE RETURN FOR FORMATTING
SET @CR = CHAR(10)
SET @OutputResult = ''

-- Some processing
SET @OutputResult = @OutputResult + 'Insert OK ' + @CR

-- Some more processing
SET @OutputResult = @OutputResult +
'Update OK. There were ' + @@ROWCOUNT + ' rows updated.' + @CR

RETURN

You will need to plan on receiving the Output parameter properly. It's a little different for ASP/ADO than for SQL.

There are papers on the sqlteam website that describe handling output parameters.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources