Programming fun in database (SQL SERVER 2008)

When I was thinking about the programming logic ,I suddenly remember my old graduation days where i generally used to run the program in C/CPP the programs like printing of * in piramid or in reverse of it. as now I am SQL Developer I tried to implement the same in SQL SERVER 2008.

I tried the above code, if you find other approch or find some any good solution please replly here.

Please run the following or attached code in text mode of SQL SERVER MANAGEMENT STUDIO 2008.

--/*  code is generated on the basis of geralization method

      --    you can check by taking as much as column you want and check like

      --    say suppose i am taking 9 columns where i am inserting * it is printing Pyramid like

      --    but while running following code select Result to text

      --    * * * * * * * * *

      --      * * * * * * *

      --        * * * * *

      --          * * *  

      --           * *

      --            *

      --*/

     

      IF(EXISTS(SELECT OBJECT_ID('Astrick')))

            DROP TABLE Astrick

      CREATE TABLE Astrick

      (

            id INT IDENTITY(1,1)

            ,C1 VARCHAR(10)

            ,C2 VARCHAR(10)

            ,C3 VARCHAR(10)

            ,C4 VARCHAR(10)

            ,C5 VARCHAR(10)

            ,C6 VARCHAR(10)

            ,C7 VARCHAR(10)

            ,C8 VARCHAR(10)

            ,C9 VARCHAR(10)

      )

      DECLARE @i INT,@i1 INT,@HOLD INT,@i2 INT,@HOLD2 INT,@loop INT

      DECLARE @INSERT VARCHAR(MAX)   -- column name of insert query

      DECLARE @Insert_Query VARCHAR(MAX) --- Insert query

      DECLARE @VALUE VARCHAR(MAX)    --- value part of insert query

      SET @I1=1

      SELECT @I2=COUNT(*) FROM sys.columns S WITH(NOLOCK)

      JOIN sys.all_objects O WITH(NOLOCK) ON S.object_id=O.object_id

      WHERE O.name LIKE 'Astrick' and collation_name is not null

      SET @loop = @i2

      SET @i = @i2

      SET @INSERT=''

      SET @VALUE=''

      WHILE(@i >0)

      BEGIN

            SET @HOLD = @I1 

            SET @HOLD2 = @I2

            WHILE(@I1 <=@I2)

            BEGIN         

                  IF(@I1=@I2)

                  BEGIN

                        SET @INSERT = @INSERT + 'C'+CONVERT(VARCHAR(10),@I1)       

                        SET @VALUE=@VALUE +'''     *'''

                  END

                  ELSE

                  BEGIN

                        SET @INSERT=@INSERT + 'C'+CONVERT(VARCHAR(10),@I1)+','

                        SET @VALUE =@VALUE + '''     *'','

                  END

                  SET @I1 = @I1 +1

            END

           

            SET @Insert_Query = 'INSERT INTO Astrick('+@INSERT+') VALUES('+@VALUE+')'

           

            EXECUTE(@Insert_Query)

 

            SET @i = @i -2

            SET @I1 = @HOLD + 1

            SET @I2 = @HOLD2 -1

            SET @INSERT=''  -- resetting string to blank to use it agin in the same loop

            SET @VALUE=''

      END

      --- code to print Asteric table

      SET NOCOUNT ON

      DECLARE @Asterik_Code VARCHAR(MAX),@Select_Code VARCHAR(MAX)

      DECLARE @c INT   

      SET @c=1

      SET @Asterik_Code =''

      WHILE(@c<=@loop)

      BEGIN   

            IF(@c=@loop)

            BEGIN

             SET @Asterik_Code=@Asterik_Code+ 'isnull('+ ( SELECT a.name FROM (

             SELECT s.name,ROW_NUMBER() OVER(ORDER BY s.name) id FROM sys.columns S WITH(NOLOCK)

             JOIN sys.all_objects O WITH(NOLOCK) ON S.object_id=O.object_id

             WHERE O.name LIKE 'Astrick' and collation_name is not null) a WHERE a.id=@c) +','''')'                                    

            END

            ELSE

            BEGIN

            SET @Asterik_Code=@Asterik_Code+ 'isnull('+ ( SELECT a.name FROM (                        

            SELECT s.name,ROW_NUMBER() OVER(ORDER BY s.name) id FROM sys.columns S WITH(NOLOCK)

            JOINys.all_objects O WITH(NOLOCK) ON S.object_id=O.object_id

            WHERE O.name LIKE 'Astrick' and collation_name is not null) a WHERE a.id=@c) +',''''),'                                 

            END      

            SET @c = @c + 1

      END

      SET @Select_Code = 'SELECT '+@Asterik_Code +' FROM Astrick'

      EXECUTE(@Select_Code)
Ebook Download
View all
Learn
View all