ALTER PROCEDURE [dbo].[Mulsp] @Size INT
AS
BEGIN
DECLARE @X INT=2
DECLARE @Y INT=1
BEGIN try
DROP TABLE [dbo].[multable]
END try
BEGIN catch
END catch
CREATE TABLE [dbo].[multable]
(
c1 INT
)
–create the rows
DECLARE @CN NVARCHAR(max)=”
DECLARE @TSql NVARCHAR(max)
WHILE @x <= @Size
BEGIN
SELECT @cn = ‘c’ + Cast(@x AS NVARCHAR)
PRINT @cn
SET @TSql=‘alter table MulTable add ‘ + @cn + ‘ int’
PRINT @Tsql
EXEC sys.Sp_sqlexec
@Tsql
SET @x=@x + 1
END
–at this point build each line query
SET @X=1
DECLARE @EachLineQuery NVARCHAR(max)
WHILE @Y <= @Size
BEGIN
SET @EachLineQuery=‘insert into MulTable values (‘
WHILE @x < @Size
BEGIN
SET @EachLineQuery=@EachLineQuery + Cast(@x*@y AS NVARCHAR)
+ ‘,’
SET @x=@x + 1
END
SET @EachLineQuery=@EachLineQuery + Cast(@x*@y AS NVARCHAR)
+ ‘)’
EXEC sys.Sp_sqlexec
@EachLineQuery
PRINT @EachLineQuery
SET @EachLineQuery=”
SET @y=@y + 1
SET @x=1
END
SELECT *
FROM multable
END