Stored Procedures

PROCEDURE TO CHECK WETHER GIVEN STRING IS PALENDROM OR NOTL:

CREATE  PROC USP_PALENDROMSTRING(@STRING VARCHAR(50))
AS
BEGIN
DECLARE @OUTPUT VARCHAR(20) =''
DECLARE @COUNT INT=LEN(@STRING)
DECLARE @I INT=1
WHILE(@I<=@COUNT)
BEGIN
SELECT @OUTPUT=@OUTPUT+SUBSTRING(@STRING , @COUNT,1)
SET @COUNT=@COUNT-1
END
PRINT @OUTPUT
IF(@OUTPUT=@STRING)
BEGIN
PRINT CHAR(13) +'GIVEN STRING IS PALINDROM'
END
ELSE
BEGIN
PRINT CHAR(13) +'GIVEN STRING IS  NOT A PALINDROM'
END
END

--Execution

--Exec USP_PALENDROMSTRING 'Varun'

--Output:
--nuraV

REVERSING GIVEN STRING:

IN THIS EXAMPLE WE ARE GOING TO USE SUBSTRING( ) AND  LEN( ) IS USED FOR COUNTING THE COUNT  OF CHARECTERS IN THE STRING

--REVERSING GIVEN STRING WITH OUT USING REVERSE FUNCTION:

DECLARE @OUTPUT VARCHAR(20) =''
DECLARE @STRING VARCHAR(20) ='Varun'
DECLARE @COUNT INT= LEN( @STRING)
DECLARE @I INT=1
WHILE(@I<=@COUNT)
BEGIN
SELECT @OUTPUT=@OUTPUT+SUBSTRING( @STRING, @COUNT, 1)
SET @COUNT=@COUNT-1
END
PRINT @OUTPUT

--Output:
--nurav

--OTHER WISE SOLVE THIS PROBLEM USING REVERSE FUNCTION ALSO AS FALLOWS:

SELECT REVERSE('Varun')

--Output:
--nurav


PROCEDURE TO GENERATE THE EVEN AND ODD NUMBER GENERATE IN BETWEEN THE SERIES:

--A SIMPLE PROCEDURE FOR GENARATING A SERIES OF ODD AND EVEN NUMBER

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROC [DBO]. [EVEN_ODD] (@NUM1 INT, @NUM2 INT)
AS
BEGIN
WHILE (@NUM1<=@NUM2)
 BEGIN
 IF ((@NUM1%2) =0)
  BEGIN
      PRINT CAST (@NUM1 AS VARCHAR (10)) +' EVEN NUMBER'
  END
  ELSE
   BEGIN
  PRINT CAST (@NUM1 AS VARCHAR (10)) +' ODD NUMBER'
   END
  SET @NUM1=@NUM1+1
 END
END

--EXECUTION:
--EXEC EVEN_ODD 10,500

---FOR FINDING A SINGLE NUMBER PASS SAME NUMBER IN TWO TIME FOR PROCEDURE

REVERSING GIVEN NUMBER WITHOUT USING REVERSE FUNCTION
DECLARE @OUTPUT VARCHAR(20) =''
DECLARE @I INT=201, @J INT =1
WHILE(@I>0)
BEGIN
SET @J=@I%10---RETURNS REMAINDER VALUE
SELECT @OUTPUT=@OUTPUT+CAST(@J AS VARCHAR(10))
SET @I=@I/10-- RETURN quotient VALUE
END
PRINT @OUTPUT

-- RESULT:
  -- 102


PROCEDURE FOR PALENDROM NUMBER

--PROCEDURE FOR PALENDROM  NUMBER       --DATE: --/--/-- --: --: --


CREATE  PROC USP_PALENDROMNUMBER(@num int)
AS
BEGIN
DECLARE @OUTPUT VARCHAR (20) =''
DECLARE @I INT=@NUM , @J INT =1
WHILE (@I>0)
BEGIN
SET @J=@I%10---RETURNS REMAINDER VALUE
SELECT @OUTPUT=@OUTPUT+CAST(@J AS VARCHAR(10))
SET @I=@I/10-- RETURN quotient VALUE
END
PRINT @OUTPUT
IF (@OUTPUT=CAST (@num AS VARCHAR (10)))
BEGIN
PRINT @OUTPUT+CHAR (13) +'GIVEN STRING IS PALINDROM'
END
ELSE
BEGIN
PRINT CHAR (13) +'GIVEN STRING IS  NOT A PALINDROM'
END
END

\--Execution
--EXEC USP_PALENDROMNUMBER 143
--Output:
--341
--GIVEN STRING IS  NOT A PALINDROM


PROCEDURE TO FIND GIVEN NUMBER IS EVEN OR ODD

CREATE PROC USP_ODD_EVEN (@NUM VARCHAR(20) )
AS
BEGIN
DECLARE @I INT=@NUM
WHILE (@I>0)
BEGIN
IF (@I%2=0)
BEGIN
PRINT CAST (@I AS VARCHAR (5)) +SPACE (2) +' IS EVEN NUMBER'
BREAK
END
ELSE
BEGIN
PRINT CAST (@I AS VARCHAR (5)) +SPACE (2) +' IS ODD NUMBER'
BREAK
END
END
END

EXECUTION:

EXEC USP_ODD_EVEN 123

RESULT:

123 IS ODD NUMBER

procedure for printing series of number in a single line

procedure for printing series of number in a single line
---------------------------------------------------------------------------------

alter proc usp_singleline(@count int)
as
begin
declare @i int=1
declare @str varchar(1000)=''
while(@i<=@count)
begin
SELECT @str=@str+', '+CAST (@i AS VARCHAR(10))
set @i=@i+1
end
print stuff(@str,1,1,'')
end
----------------
EXECUTION:

exec usp_singleline 16

PRINT THE COLUMNS DATA INTO A SINGLE STRING

FISRT CREATE A #TEMP TABLE WITH AN IDENTITY COLUMN AND WITH REQUIRED COLUMN NAMES
PLACE @TBL_NAME = TABLE NAME AS U WANT=#TEMP

AND @COL_NAME =AS  U WANT TO FIND FOR COLUMN

DECLARE @STRING VARCHAR(MAX)=''
DECLARE @FSTRING VARCHAR(MAX)
DECLARE @I INT=1
DECLARE @CNT INT=(SELECT COUNT(*) FROM @TBL_NAME)
WHILE(@I<=@CNT)
BEGIN
SELECT @FSTRING=(SELECT @COL_NAME FROM EI WHERE ID=@I)
SET @STRING=@STRING+','+ @FSTRING
SET @I=@I+1
END
PRINT STUFF(@STRING,1,1,'')

grouping by multiple columns to a single string:

Grouping by Multiple Columns to Single Column as A String

CREATE TABLE TEST (ID INT, NAME VARCHAR(4))
GO
INSERT INTO TEST (ID, NAME)
SELECT 1, 'A'
UNION
SELECT 1, 'B'
UNION
SELECT 1, 'C'
UNION
SELECT 2, 'A'
UNION
SELECT 2, 'B'
UNION
SELECT 2, 'C'
UNION
SELECT 2, 'D'
UNION
SELECT 2, 'E'
GO
SELECT *
FROM TEST
GO

----1.  PRINT THE TABLE AS NAMES ARE IN ONE LINE BASED ON ID 

SELECT T.ID, STUFF((SELECT ',' + S.NAME FROM TEST S WHERE S.ID = T.ID FOR XML PATH('')),1,1,'') AS DATA    FROM TEST AS T  GROUP BY T.ID

ID          OUTPUTS
1           A, B, C
2           A, B, C, D, E

--2. FUNCTION TO PRINT THE TABLE AS NAMES ARE IN ONE LINE BASED ON ID

 CREATE FUNCTION FNMAKELIST
   (  @ID INT  )
   RETURNS VARCHAR(1000)
AS
   BEGIN
      DECLARE @TEMP TABLE
         (  NAME VARCHAR(20)  )
      DECLARE @RESULT VARCHAR(1000)
      SET @RESULT = ''
      INSERT INTO @TEMP
         SELECT NAME
         FROM TEST
         WHERE ID = @ID
      IF @@ROWCOUNT > 0
         UPDATE @TEMP
            SET @RESULT = ( @RESULT + NAME + ', ' )
      RETURN SUBSTRING ( @RESULT, 1, ( LEN ( @RESULT ) - 1 ))
   END

EXECUTION:

SELECT   ID , OUTPUTS = DBO.FNMAKELIST ( ID ) FROM TEST  GROUP BY ID

ID          OUTPUTS
1           A, B, C
2           A, B, C, D, E

create proc prime(@no int)
as
begin
declare @i int
declare @j int
declare @cnt int
set @i=1
while(@i<=@no)
begin
set @cnt=0
set @j=1
while (@j<=@i)
begin
if (@i % @j=0)
set @cnt=@cnt+1
set @j=@j+1
end
if (@cnt=2)
print @i
set @i=@i+1
end
end

select *,(select count(1) from emp1 e where e.deptno=d.deptno)as cnt from depts d 

To Genarate Z to A

alter proc usp_generate_ztoa as
begin
declare @i int=90
declare @Z int=@i-25
while(@i>=@Z)
begin
print char(@i)-' '-char(@i-32)
set @i=@i-1
end
end
exec usp_generate_ztoa @Z=65
select ASCII('a')-ASCII('A')
create proc usp_generate_