Stored procedure - how to return a single table

• lukavog

  Valued Member

  Points: 57

  More actions

  November 21, 2013 at 11:16 am

  #280406

  Hi 😛

  I have this SP

  ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)

  AS

  DECLARE @steps bigint

  SET @steps = DATEDIFF(hour, @start, @stop)/ @step

  DECLARE @i bigint

  SET @i=0

  DECLARE @tempStep varchar

  SET @tempStep = DATEPART(hour, @start)

  WHILE @i<@steps BEGIN

  SELECT (@tempStep)

  SET @tempStep = @tempStep + @step;

  SET @i = @i+1

  END

  Callin the SP

  DECLARE @start datetime = convert(datetime, '2013-05-24 00:00:00', 120);

  DECLARE @stop datetime = convert(datetime, '2014-05-24 23:59:59', 120);

  execute dbo.GetDelayIntervalData @start, @stop, 1;

  Return "like a" bunch of tables (1X1)... how to output like a single result, concatenated table of SELECT (@tempStep)?!

  Thanks Luka

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 21, 2013 at 12:11 pm

  #1668216

  lukavog (11/21/2013)

  ---

  Hi 😛

  I have this SP

  ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)

  AS

  DECLARE @steps bigint

  SET @steps = DATEDIFF(hour, @start, @stop)/ @step

  DECLARE @i bigint

  SET @i=0

  DECLARE @tempStep varchar

  SET @tempStep = DATEPART(hour, @start)

  WHILE @i<@steps BEGIN

  SELECT (@tempStep)

  SET @tempStep = @tempStep + @step;

  SET @i = @i+1

  END

  Callin the SP

  DECLARE @start datetime = convert(datetime, '2013-05-24 00:00:00', 120);

  DECLARE @stop datetime = convert(datetime, '2014-05-24 23:59:59', 120);

  execute dbo.GetDelayIntervalData @start, @stop, 1;

  Return "like a" bunch of tables (1X1)... how to output like a single result, concatenated table of SELECT (@tempStep)?!

  Thanks Luka

  What is this supposed to be doing? Do you just want to get a table with all the values at once? You should use a tally table and drop the looping from this.

  Here is how your entire process might look using a cte style tally table built on the fly inside your proc.

  create PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int) as

  WITH

  E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

  E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

  E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

  Tally(N) AS

  (

  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

  )

  select * from Tally where N < DATEDIFF(hour, @start, @stop)/ @step

  go

  execute dbo.GetDelayIntervalData '2013-05-24 00:00:00', '2014-05-24 23:59:59', 1;

  You can read more about tally tables here. http://www.sqlservercentral.com/articles/62867/[/url]

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/