function to use comma seprated values in to a table so that it can be use in IN statement in sql

function to use comma seprated values in to a table so that it can be use in IN statement in sql

ALTER FUNCTION [dbo].[fnNTextToIntTable] (@Data nvarchar(4000))
RETURNS
    @IntTable TABLE ([Value] NVARCHAR(500) NULL)
AS
BEGIN
    SET @Data=Replace(@Data,'$',',')
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)
    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1
    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)
        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(RTRIM(LTRIM(@vv)), '') + @v
        END
        SET @Ptr = @Ptr + 1
    END
    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
    RETURN
END

calculate age or get the duration between two dates in years, months and days

ALTER FUNCTION [dbo].[calc_age]
(
-- Add the parameters for the function here
@date smalldatetime
)
RETURNS nvarchar(4000)
AS
BEGIN
-- Declare the return variable here
DECLARE  @tmpdate datetime, @years int, @months int, @days int

SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
return  CAST(@years as nvarchar(50)) + ' Years '  + CAST(@months as nvarchar(50)) + ' Months ' + CAST(@days as nvarchar(50)) +' Days '
END

USE SUBQUERY IN INNER JOIN

create table tblUser
 (
  UserId int identity(1,1) not null,
  UserName nvarchar(500),
  Age int

 )

 INSERT INTO tblUser select 'Rahul',26
 INSERT INTO tblUser select 'Yunus',26


 create table tblTransaction

  (
   TransactionId int identity(1,1) not null,
   UserId int,
   Amount int ,
   Status bit
  )

  INSERT INTO tblTransaction SELECT 1,200,1
  INSERT INTO tblTransaction SELECT 1,2100,1
  INSERT INTO tblTransaction SELECT 1,900,1
  INSERT INTO tblTransaction SELECT 1,60,1



  INSERT INTO tblTransaction SELECT 2,200,1
  INSERT INTO tblTransaction SELECT 2,2100,1
  INSERT INTO tblTransaction SELECT 2,900,1
  INSERT INTO tblTransaction SELECT 2,60,1
  INSERT INTO tblTransaction SELECT 2,55,1




SELECT * FROM tblUser
SELECT * FROM tblTransaction

SELECT * FROM tblUser as U
INNER JOIN
(
SELECT * FROM tblTransaction
WHERE
       TransactionId IN
               (
                       SELECT MAX(TransactionId)
                       FROM
                               tblTransaction
                       GROUP BY UserId
               )
) AS [A]
ON
U.UserId=A.UserId

http://sqlfiddle.com/#!3/85d95/6

Use string type comma separated in IN clause in sql

 declare @ProductIds nvarchar(50)='18,19'

SELECT * FROM products Where (',' + @ProductIds +',' LIKE '%,' + CONVERT(VARCHAR, ProductId) + ',%')