How to output the results of an procedure in SQL server?

How to output the results of a stored procedure with a while loop MS SQL Server?

• I'm performing some calculations on tsunami's wave time between different radii. I'm having trouble outputting the results of the stored procedure into a table. The output I get is a blank table without any data stored. The log says "0 rows affected". For some reason my inputs and outputs are not registering. I thinking it could have something to do with the way I'm using the loop inside to SP. CREATE PROCEDURE SP_Tsunami ( @oceanDepth int, @radii1 int, @radii2 int, @tsunamiSpeed int OUTPUT, @tsunamiTimeDifference int OUTPUT ) AS BEGIN SET @tsunamiSpeed = sqrt(32.1725 * @oceanDepth) * (60.0/88.0); Truncate Table dbo.Tsunami DECLARE @i int SET @i = 0; WHILE (@i <= 10000) BEGIN INSERT INTO dbo.Tsunami (Radius, Wavetime) VALUES (@i, (@i / @tsunamiSpeed)) SET @i = @i + 100; END DECLARE @tsunamiTime1 int DECLARE @tsunamiTime2 int SET @tsunamiTime1 = (Select Wavetime From Tsunami WHERE Radius = @radii1); SET @tsunamiTime2 = (Select Wavetime From Tsunami WHERE Radius = @radii2); SET @tsunamiTimeDifference = (@tsunamiTime2 - @tsunamiTime1); END /* Outputs */ DECLARE @Out_tsunamiSpeed int DECLARE @Out_tsunamiTimeDifference int /* Inputs */ DECLARE @IN_oceanDepth int DECLARE @IN_radii1 int DECLARE @IN_radii2 int SET @IN_oceanDepth = 15088; SET @IN_radii1 = 2500; SET @IN_radii2 = 7500; Execute SP_Tsunami @oceanDepth = @IN_oceanDepth, @radii1 = @IN_radii1, @radii2 = @IN_radii2, @tsunamiSpeed = @Out_tsunamiSpeed OUTPUT, @tsunamiTimeDifference = @Out_tsunamiTimeDifference OUTPUT

• Answer:

  Doesn't make much sense to insert the data into table and then read it from there. All the rows you are inserting into your table eventually only one row is being read from the table to assign values to variables. Also change the procedure name prefix from sp_ to something else, sp_ is system stored procedure prefix. I have changed the procedure definition a little bit hope it makes sense. ALTER PROCEDURE SP_Tsunami ( @oceanDepth int, @radii1 int, @radii2 int, @tsunamiSpeed int OUTPUT, @tsunamiTimeDifference int OUTPUT ) AS BEGIN SET @tsunamiSpeed = sqrt(32.1725 * @oceanDepth) * (60.0/88.0); DECLARE @tsunamiTime1 int DECLARE @tsunamiTime2 int SET @tsunamiTime1 = @radii1 / @tsunamiSpeed; SET @tsunamiTime2 = @radii2 / @tsunamiSpeed; SET @tsunamiTimeDifference = (@tsunamiTime2 - @tsunamiTime1); END GO Execute the procedure with your provided data. /* Outputs */ DECLARE @Out_tsunamiSpeed int DECLARE @Out_tsunamiTimeDifference int /* Inputs */ DECLARE @IN_oceanDepth int DECLARE @IN_radii1 int DECLARE @IN_radii2 int SET @IN_oceanDepth = 15088; SET @IN_radii1 = 2500; SET @IN_radii2 = 7500; Execute SP_Tsunami @oceanDepth = @IN_oceanDepth , @radii1 = @IN_radii1 , @radii2 = @IN_radii2 , @tsunamiSpeed = @Out_tsunamiSpeed OUTPUT , @tsunamiTimeDifference = @Out_tsunamiTimeDifference OUTPUT SELECT @Out_tsunamiSpeed AS Out_tsunamiSpeed ,@Out_tsunamiTimeDifference AS Out_tsunamiTimeDifference Result Set: Out_tsunamiSpeed Out_tsunamiTimeDifference 475 10