0

How to dynamic generate row from max value for example if i am passing max value 7 Store procedure should return value 1 2 3 4 5 6 7

without using loops

Mansi Mistry
  • 189
  • 1
  • 9
  • 1
    Answer [here](https://stackoverflow.com/a/51320368/8298316) – AnkurSaxena Feb 06 '21 at 06:14
  • 2
    In case you are unaware, stored procedures are not standard SQL. Each DBMS has its own, proprietary language for stored procedures. For example, Oracle has PL/SQL and SQL Server has T-SQL. So if you want a relevant answer, I think that you should [edit] your question and write which DBMS you are using. Is it MySQL? – Abra Feb 06 '21 at 06:20
  • Tag your question with the database youa re using. – Gordon Linoff Feb 06 '21 at 12:54
  • Thank you for suggetions will definately improve my question that gives clear understand of what i am trying to ask. – Mansi Mistry Feb 08 '21 at 09:25
  • I added the `sql-server` tag based on the syntax in the accepted answer. –  Feb 08 '21 at 11:29

2 Answers2

0

If you are using sql server below query will serve you purpose:

create procedure generate_list 
@maxLimit int 
as
begin
SELECT DISTINCT n = number 
FROM master..[spt_values] 
WHERE number BETWEEN 1 AND @maxlimit
end

Then call the store procedure with :

exec generate_list 7

Output:

enter image description here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • 1
    Please, [do not post images of code or data](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question#285557). – astentx Feb 06 '21 at 06:40
  • I have provided code in code section. The image is to share the output of that code. Thanks for your suggestion. – Kazi Mohammad Ali Nur Romel Feb 06 '21 at 06:45
  • 1
    You can use [ASCII table generator](https://ozh.github.io/ascii-tables/) to show the output. Or just format 7 rows with 1 column manually by putting pipes (`|`) around the header and values and `|-|` after the header to create tabular output. Or create [a fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019l). – astentx Feb 06 '21 at 06:58
0
create PROCEDURE [dbo].GenerateSequence 
    @MaxLimit int
    
AS
BEGIN

;with numcte
AS  
(  
  SELECT 1  [Sequence] 
  UNION all  
  SELECT  [Sequence] + 1 FROM numcte WHERE [Sequence] < @MaxLimit
)
select * from numcte

END
Mansi Mistry
  • 189
  • 1
  • 9