I want to create a stored procedure in SQL server where I will pass a parameter "N type int" and then procedure will create multiplication tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

lets say N=2

expected output

and then in next column i want sum of both columns or n number of columns.

What I have tried:

SQL

ALTER procedure [dbo].[project]

	@N int

AS

BEGIN
	declare @tbl1 table(ID int identity(1,1), MT int )
	declare @tbl2 table(ID int identity(1,1), MT int)
	declare @T int
	declare @i int, @j int
	set @i=1
	while @i<=@N
	begin			
		set @j=1
			while @j<=10
			begin
				set @T=@i*@j
				insert into @tbl1 values(@T)
				set @j=@j+1
			end
		select *from @tbl1
		set @i=@i+1
	end	
END

above code creates multiplication output but in different tables, please suggest a solution with query.

Posted 16-Mar-18 20:34pm

Siddharth_Gupta

Updated 17-Mar-18 2:56am

Maciej Los

v2

Add a Solution

Comments

Siddharth_Gupta 17-Mar-18 5:44am

I guess I figured a way around the problem, below is the code I used:
ALTER procedure [dbo].[project]

@N int

AS

BEGIN
declare @tbl table (ID int identity(1,1),x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int, x8 int, x9 int, x10 int,sumall int)
declare @tbl1 table (ID int identity(1,1),ttl int)
declare @amount int
declare @T int
declare @x int, @y int
set @x=1
set @y=1
while @y<=@N
begin
set @T=@x*@y
insert into @tbl values(@T, @T*2, @T*3, @T*4, @T*5, @T*6, @T*7, @T*8, @T*9, @T*10,@T+ @T*2+ @T*3+ @T*4+ @T*5+ @T*6+ @T*7+ @T*8+ @T*9+ @T*10)
end
select *from @tbl
END

//If anyone has a better work around then please do share//

#realJSOP 17-Mar-18 11:54am

Look at my solution.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2018-03-17T02:02:00

Try this:

SQL

DECLARE @n INT = 2;

;WITH cte AS
(
    -- create a common table expression with ten rows with the "Value" column 
    -- being set to the row number
    SELECT TOP(10) Value = ROW_NUMBER() 
    OVER (ORDER BY[object_id]) FROM SYS.ALL_OBJECTS
)
-- and then select the rows, identify the mulitplier (@n), and set the result
SELECT Value,
       @n AS Multiplier,
       Value * @n AS Result
FROM   cte;

You should end up with 10 rows that show the value (1-10), the multiplier (in this case, it's 2), and the result of multiplying the value by the multiplier.

If you want more than 10 rows, just change the TOP(10) clause to the reflect the desired value.