How to print star in SQL SERVER

By
T SQL SQL Server Print
In our college so many time we heard about  print the star in different different shape with different different programming language.Some one do this on C, Some on C++, C# ,java,python etc.
We do this with our favorite language which was known by us. But i am sure no one can write it in T SQL.In T SQL we are aware about the while loop with select statement,recursion,Common Tabular Expression  which is also known as CTE.I write a T-SQL With the help of CTE we discuss CTE later. Right now i am focus only on the t sql to print the star in MS Sql server.

Code Sample [SQL SERVER Query]


;With CTE(star)
AS
(
SELECT CAST('*' as varchar(10)) as star
UNION ALL
SELECT CAST(star +'*' as varchar(10)) FROM CTE
WHERE LEN(star)<10
)

SELECT * FROM CTE

Output of print star exmaple

*
**
***
****
*****
******
*******
********
*********
**********

Explanation

Here I am using CTE common tabular expression .It basically use for recursion. Here i set by default length of star is 10. if you want to make it dynamic then query is


DECLARE @starLength int
SET @starLength=20 -- here you put any number of length
;With CTE(star)
AS
(
SELECT CAST('*' as varchar(max)) as star
UNION ALL
SELECT CAST(star +'*' as varchar(max)) FROM CTE
WHERE LEN(star)<@starLength
)


SELECT * FROM CTE

Note: If you use @starLength more then 101 then its throw error like
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.To solve this we have to set OPTION(MAXRECURSION 0) in our query like below
SELECT * FROM CTE OPTION(MAXRECURSION 0)

2 comments: