User Defined Table Types and its use

Programmability > Types > User-Defined Table Types

================
USE [GGGG]
GO


CREATE TYPE [pppp].[mmmm] AS TABLE(
[AAA] [varchar](20) NULL,
[BBB] [int] NULL,
[CCC] [varchar](500) NULL,
[DDD] [nvarchar](2000) NULL,
[EEE] [varchar](50) NULL,
[FFF] [int] NULL,
[GGG] [bit] NULL
)
GO
=========================== use =======================

USE [pmpm]
GO

/****** Object:  StoredProcedure [pppp].[mmmm]    Script Date: 11/03/2011 13:54:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [pppp].[mmmm]
@cdd [pppp].[mmmm] READONLY
AS

DECLARE @AAA AS [varchar](20)
DECLARE @BBB AS [int]
DECLARE @CCC AS [varchar](500)
DECLARE @DDD AS [nvarchar](2000)
DECLARE @EEE AS [varchar](50)
DECLARE @FFF AS [int]
DECLARE @GGG AS [bit]


IF NOT EXISTS(
SELECT  A1.AAA  
FROM @cdd A1
INNER JOIN @cdd A2
ON (A1.AAA=A2.AAA) AND
((A1.BBB <>3 AND A2.BBB=3)OR
(A1.BBB =3 AND A2.BBB<>3)OR
((A1.BBB <>3 AND A2.BBB<>3))AND(A1.BBB=A2.BBB))
group by A1.AAA
having COUNT(*)> 2
 ) AND
((SELECT COUNT(*)  FROM @cdd)> 0)


BEGIN
DELETE FROM [pmpm].[Reports].[TTTTT]
WHERE EEE1 = 'CCD'AND DATEDIFF(MI,created_datetime,GETDATE())>1

INSERT INTO [pmpm].[Reports].[TTTTT]
( [AAA1]
  ,[BBB1]
  ,[CCC1]
  ,[DDD1]
  ,[EEE1]
  ,[FFF1]          
  ,[active_flag]
  )
SELECT
A.AAA,A.BBB,A.CCC,A.DDD,A.EEE,A.FFF,A.GGG
FROM @cdd A left OUTER JOIN
[Reports].[TTTTT] w ON A.AAA = w.AAA1
WHERE
(A.BBB<>3)and w.userid IS NULL



DECLARE ccd_cursor CURSOR FOR
SELECT A.AAA,A.BBB,A.CCC,A.DDD,A.EEE,A.FFF,A.GGG
FROM @cdd A
WHERE A.BBB = 3

OPEN ccd_cursor;
FETCH NEXT FROM ccd_cursor
INTO @AAA,@BBB, @CCC,@DDD,@EEE,@FFF, @GGG;

WHILE @@FETCH_STATUS = 0
BEGIN
-- If BBB1=3 username is not present in TTTTT insert the record from @cdd

  IF((SELECT COunt(*) FROM [pmpm].[Reports].[TTTTT] WHERE AAA1 = @AAA) = 0)
  BEGIN
  INSERT INTO [pmpm].[Reports].[TTTTT]
( [AAA1]
  ,[BBB1]
  ,[CCC1]
  ,[DDD1]
  ,[EEE1]
  ,[FFF1]          
  ,[active_flag]
  )
values(@AAA,@BBB, @CCC,@DDD,@EEE,@FFF, @GGG)
END
-- If BBB1=3 username is  present in TTTTT insert the CCC1 i.e., country from @cdd
IF((SELECT COunt(*) FROM [pmpm].[Reports].[TTTTT] WHERE AAA1 = @AAA) > 0)
  BEGIN
Update [pmpm].[Reports].[TTTTT]
 SET CCC1 = CCC1 +','+ @CCC
WHERE
((AAA1 = @AAA)
AND (@BBB= 3) AND (BBB1=3)
AND (CCC1 NOT LIKE  '%' + @CCC + '%'))
END

FETCH NEXT FROM ccd_cursor
INTO @AAA,@BBB,@CCC,@DDD,@EEE,@FFF,@GGG;
END
CLOSE ccd_cursor;
DEALLOCATE ccd_cursor;
END

GO