Showing posts with label Sql Scalar Valued Function. Show all posts
Showing posts with label Sql Scalar Valued Function. Show all posts

Sunday, 24 June 2018

Get Auto Increment No in Scalar Function in MS Sql

CREATE FUNCTION [dbo].[GETBookingOrderNo]()
RETURNS CHAR(9)
AS
BEGIN
DECLARE @lastval NVARCHAR(50)
SET @lastval = (SELECT MAX(BookingOrderNo) FROM BookingDetailMaster)
IF @lastval IS NULL SET @lastval = 'RO 000001'
--SET @lastval='RO 000009'
DECLARE @i INT
SET @i = RIGHT(@lastval,6) + 1
RETURN 'RO ' + RIGHT('0000' + CONVERT(VARCHAR(10),@i),6)
END
GO

SELECT [dbo].[GETBookingOrderNo]()

Monday, 26 March 2018

Split String Scalar Valued Function

CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
           
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
           
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END
      RETURN
END
GO

Test ::

SELECT Item

FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')

Thursday, 19 October 2017

Feed Back Ratings Scaler Valued Function

ALTER FUNCTION [dbo].[UserFeedBackRatings]
(
@Rating DECIMAL(18,1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @RatingsImg NVARCHAR(max);
SET  @RatingsImg  = (CASE WHEN @Rating = '0.5' THEN '<i class="fa fa-star-half" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '1.0' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '1.5' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-half" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '2.0' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '2.5' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-half" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '3.0' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '3.5' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-half" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '4.0' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-o" aria-hidden="true"></i>'
WHEN @Rating = '4.5' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star-half" aria-hidden="true"></i>'
WHEN @Rating = '5.0' THEN '<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'+'&nbsp;'+'<i class="fa fa-star" aria-hidden="true"></i>'
END)

RETURN @RatingsImg

END

TrimString Scaler Valued Function

ALTER FUNCTION [dbo].[TrimString](@string NVARCHAR(MAX),@TrimLength INT=0)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX);
SELECT @Result =  CASE WHEN (LEN(ISNULL(LTRIM(RTRIM(@string)),''))> @TrimLength)
THEN  '<span title="'+@string+'">'+ISNULL(SUBSTRING(ISNULL(LTRIM(RTRIM(@string)),''),0,@TrimLength),'')+'..</span>'
  ELSE ISNULL(LTRIM(RTRIM(@string)),'')
  END
RETURN @Result

END

Get Month Name Scalre Valued Function

CREATE FUNCTION [dbo].[GetMonthName]
(
    -- Add the parameters for the function here
    @month int
)

RETURNS varchar(10)
AS
BEGIN
    DECLARE @month_name varchar(10)

    IF @month = 1
    BEGIN
        SET @month_name = 'January';
    END
    ELSE IF @month = 2
    BEGIN
        SET @month_name = 'February';
    END
    ELSE IF @month = 3
    BEGIN
        SET @month_name = 'March';
    END
    ELSE IF @month = 4
    BEGIN
        SET @month_name = 'April';
    END
    ELSE IF @month = 5
    BEGIN
        SET @month_name = 'May';
    END
    ELSE IF @month = 6
    BEGIN
        SET @month_name = 'June';
    END
    ELSE IF @month = 7
    BEGIN
        SET @month_name = 'July';
    END
    ELSE IF @month = 8
    BEGIN
        SET @month_name = 'August';
    END
    ELSE IF @month = 9
    BEGIN
        SET @month_name = 'September';
    END
    ELSE IF @month = 10
    BEGIN
        SET @month_name = 'October';
    END
    ELSE IF @month = 11
    BEGIN
        SET @month_name = 'November';
    END
    ELSE IF @month = 12
    BEGIN
        SET @month_name = 'December';
    END
    ELSE
    BEGIN
        SET @month_name = 'Invalid month number!';
    END


    RETURN @month_name;
END

/*SELECT dbo.GetMonthName(1) as [month]

SELECT dbo.GetMonthName(month(getdate())) as [month]*/

Flag Status Scaler Valued Function

CREATE FUNCTION [dbo].[GetDeleteFlagStatus]
(
@DeleteFlag INT
)
RETURNS NVARCHAR(15)
AS
BEGIN

DECLARE @Status NVARCHAR(15);
SET @Status = (CASE WHEN @DeleteFlag=0 THEN 'Active'
WHEN @DeleteFlag=1 THEN 'Suspend'
WHEN @DeleteFlag =2 THEN 'Delete'
END)
RETURN @Status
END

Sunday, 27 August 2017

Creating Function in SQL Server

Creating Function in SQL Server

create table emp_salary(id int primary key identity(1,1), current_salary bigint )

insert into emp_salary(current_salary) values(5000)
insert into emp_salary(current_salary) values(6000)
insert into emp_salary(current_salary) values(7000)
insert into emp_salary(current_salary) values(8000)
insert into emp_salary(current_salary) values(8000)
insert into emp_salary(current_salary) values(6000)

Creating a Function for calculate next_month_salary (current_salary_2000)

create function f2(@sal int)
returns int
as
begin
declare @next_month_salary int
set @next_month_salary=@sal+2000
return @next_month_salary
end
Calling Current Month Salary
select Current_salary from emp_salary

Calling function in SQL Server
 select current_salary,dbo.f2(current_salary) as Next_month_salary from emp_salary

Wednesday, 26 July 2017

Get Name On Code With Comma Separate in MS Sql


CREATE FUNCTION [dbo].[GetNameOnCode]
(
@StrCodes NVARCHAR(MAX)='C-05465,C-00535,C-00538,C-03865,C-06377'

)
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @Separator CHAR(1)
DECLARE @SeparatorPosition INT
DECLARE @strCode NVARCHAR(50)
SET @Separator = ','
DECLARE @NewStr NVARCHAR(MAX)
DECLARE @NewName NVARCHAR(MAX)=''

SET @StrCodes=@StrCodes+','
--SELECT @StrCodes

WHILE PATINDEX('%' + @Separator + '%', @StrCodes) <> 0
BEGIN
SET @SeparatorPosition = PATINDEX('%' + @Separator + '%', @StrCodes)
SET @strCode = LEFT(@StrCodes, @SeparatorPosition - 1)

--SELECT @strCode

SET @NewStr=ISNULL((SELECT TOP 1 ISNULL(CustName,'') FROM CustomerMaster WHERE LTRIM(RTRIM(No))=LTRIM(RTRIM(@strCode))),'')
SET @NewName=@NewName+@NewStr+','

SET @StrCodes = STUFF(@StrCodes, 1, @SeparatorPosition, '')

END

RETURN @NewName
END

Get Month No Scalar Valued Function MS Sql






ALTER FUNCTION [dbo].[GetMonthNo]
(
@MonthName NVARCHAR(50)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

RETURN(CASE @MonthName
   WHEN 'january' THEN 1
   WHEN 'february' THEN 2
   WHEN 'march' THEN 3
   WHEN 'april' THEN 4
   WHEN 'may' THEN 5
   WHEN 'june' THEN 6
   WHEN 'july' THEN 7
   WHEN 'august' THEN 8
   WHEN 'september' THEN 9
   WHEN 'october' THEN 10
   WHEN 'november' THEN 11
   WHEN 'december' THEN 12
   ELSE  '' END)

END

Monday, 3 July 2017

Get Name On Code in Comma Separate List Sql



CREATE FUNCTION [dbo].[GetNameOnCode]
(
@StrCodes NVARCHAR(MAX)='Code'

)
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @Separator CHAR(1)
DECLARE @SeparatorPosition INT
DECLARE @strCode NVARCHAR(50)
SET @Separator = ','
DECLARE @NewStr NVARCHAR(MAX)
DECLARE @NewName NVARCHAR(MAX)=''

SET @StrCodes=@StrCodes+','
--SELECT @StrCodes

WHILE PATINDEX('%' + @Separator + '%', @StrCodes) <> 0
BEGIN
SET @SeparatorPosition = PATINDEX('%' + @Separator + '%', @StrCodes)
SET @strCode = LEFT(@StrCodes, @SeparatorPosition - 1)

--SELECT @strCode

SET @NewStr=ISNULL((SELECT TOP 1 ISNULL(Name,'') FROM TableName WHERE LTRIM(RTRIM(No))=LTRIM(RTRIM(@strCode))),'')
SET @NewName=@NewName+@NewStr+','

SET @StrCodes = STUFF(@StrCodes, 1, @SeparatorPosition, '')

END

RETURN @NewName
END

React Hooks - custom Hook

  v CustomHook Ø React allows us to create our own hook which is known as custom hook. Example – 1 localStorage Demo Step-1 Create ...