SQL server Capitalize First Letter

 ---Input String 'PABITRA MICROSOFT PabiTra miCroSoft'

declare @SQLColumnName nvarchar(max)

set @SQLColumnName = 'PABITRA MICROSOFT PabiTra miCroSoft'

declare @SpaceInWord char(1) = ' '

-- Upper case first letter and all others in lower case letters

select @SQLColumnName = STUFF(LOWER(@SQLColumnName), 1, 1, UPPER(LEFT(@SQLColumnName,1)) )

-- search for space character

declare @i int = CHARINDEX(@SpaceInWord, @SQLColumnName, 1)

while @i > 0

begin

 select @i = @i + 1

 -- replace character after space with its upper case letter

 select @SQLColumnName = STUFF(@SQLColumnName, @i, 1, UPPER( SUBSTRING(@SQLColumnName, @i, 1)) )

 -- search for next space character

 select @i = CHARINDEX(@SpaceInWord, @SQLColumnName, @i)

end

select @SQLColumnName

---Out Put : 'Pabitra Microsoft Pabitra Microsoft'

using cursor

----------

 

 

 declare @PrinterId int,@SQLColumnName varchar(1000)

declare _cursor cursor read_only for

select PrinterId,PAddress from PrinterDetails

open _cursor

fetch next from _cursor into @PrinterId,@SQLColumnName

while @@FETCH_STATUS=0

begin

-----------------------------Body

declare @SpaceInWord char(1) = ' '

-- Upper case first letter and all others in lower case letters

select @SQLColumnName = STUFF(LOWER(@SQLColumnName), 1, 1, UPPER(LEFT(@SQLColumnName,1)) )

-- search for space character

declare @i int = CHARINDEX(@SpaceInWord, @SQLColumnName, 1)

while @i > 0

begin

 select @i = @i + 1

 -- replace character after space with its upper case letter

 select @SQLColumnName = STUFF(@SQLColumnName, @i, 1, UPPER( SUBSTRING(@SQLColumnName, @i, 1)) )

 -- search for next space character

 select @i = CHARINDEX(@SpaceInWord, @SQLColumnName, @i)

end

--select @SQLColumnName

update PrinterDetails set PAddress=@SQLColumnName where PrinterId=@PrinterId

 -----------------------------Body end 

fetch next from _cursor into  @PrinterId,@SQLColumnName

end

close _cursor

deallocate _cursor

SQL queries to change the column datatype type

 Column with data type : AadhaarNumner int 

SQL queries to change the column datatype type

Alter table dbo.OrderPaperBooks alter column AadhaarNumner varchar(20)

SQL query for alter column name

 table name: dbo.OrderPaperBooks

old column name : PPNoOfCopy

assign new column name :NoOfCopy 

SQL query for alter column name 

 EXEC sp_rename 'dbo.OrderPaperBooks.PPNoOfCopy', 'NoOfCopy', 'COLUMN';

drop and create SQL Server procedure simple way

 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'USP_BookTypes')

DROP PROCEDURE USP_BookTypes

GO

CREATE  PROC USP_BookTypes

(

   @action varchar(20)

  ,@BookTypeId int=null

  ,@BookType varchar(100)=null

)

AS BEGIN

  IF(@action='SELECTALL')

   BEGIN

    SELECT B.BookTypeId,B.BookType FROM BookTypes B WITH(NOLOCK)  

WHERE B.IsActive='Y'  order by B.BookTypeId; 

   END

   ELSE IF(@action='SELECTBYID')

   BEGIN

    SELECT B.BookTypeId,B.BookType FROM BookTypes B WITH(NOLOCK)  

WHERE B.BookTypeId=@BookTypeId and B.IsActive='Y' order by B.BookTypeId; 

   END

 END