Difference beteween stuff and replace function in sql server

STUFF function is used to overwrite existing characters. Here is the syntax of stuff function

stuff (Expression, start, length, replace),

Expression is the string that will have characters substituted

start is the starting position

length is the number of characters in the string that are substituted

replace are the new characters interjected into the string

Here is the   example of stuff function

SELECT STUFF('anil kumar', 1, 2, 'sun');

Output this query = sunil kumar

REPLACE function to replace existing characters of all occurrences.

Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Here  is the example

select REPLACE('Sunil Kumar asp.net devloper','Sunil','Anil' )

output of this query is: Anil Kumar asp.net devloper

ORDER BY Clause using Variable in store Procedure

Let’s create the table with sample data:

CREATE TABLE [dbo].[Product] (

    [Id]          INT            IDENTITY (1, 1) NOT NULL,

    [Name]        NVARCHAR (256) NULL,

    [CategoryId]  INT            NULL,

    [Description] NVARCHAR (512) NULL,

    [IsActive]    BIT            NULL,

    [Price]       DECIMAL (18)   NOT NULL,

    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [FK_Product_Category] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Category]([Id]) ON DELETE CASCADE ON UPDATE CASCADE

);

//Insert Data

SET IDENTITY_INSERT [dbo].[Product] ON

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (1, N'Htc on', 1, NULL, 1, CAST(34 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (2, N'Samsung  galaxy', 1, N' Thids jsdfhns kndfksd', 1, CAST(45 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (3, N'Lumia 1320', 1, N'test test', 1, CAST(45 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (4, N'Ramond', 2, N' theisbdf ', 1, CAST(45 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (5, N'Peter England trouser', 2, N'sdfj', 1, CAST(4 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (6, N'Niva', 3, N'dhfdh', 1, CAST(34 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive], [Price])VALUES (7, N'Ponds', 3, NULL, 1, CAST(45 AS Decimal(18, 0)))

SET IDENTITY_INSERT [dbo].[Product] OFF

//Store Procedure

create PROCEDURE [dbo].[Sorting]

      

       @SortByCloumnName varchar(128),

       @SortByDirection  varchar(4)

AS

    

       select *  from  Product   order by 

          case when @SortByCloumnName='Name' and  LOWER( @SortByDirection)= 'asc'  thenName   end asc,

           case when @SortByCloumnName='Name' and LOWER(@SortByDirection) = 'desc'  thenName   end Desc

      

         

         RETURN 0