ÿþUSE [AdventureWorks]

GO

/****** Object:  Table [dbo].[pTable]    Script Date: 04/23/2011 19:11:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[pTable](

	[p2k] [int] NOT NULL,

	[p2l] [varchar](50) NOT NULL,

	[p1k] [int] NOT NULL,

	[p1l] [varchar](50) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

INSERT [dbo].[pTable] ([p2k], [p2l], [p1k], [p1l]) VALUES (1, N'q', 1, N'a')

INSERT [dbo].[pTable] ([p2k], [p2l], [p1k], [p1l]) VALUES (2, N'w', 1, N'a')

INSERT [dbo].[pTable] ([p2k], [p2l], [p1k], [p1l]) VALUES (3, N'e', 2, N'b')

/****** Object:  Table [dbo].[fTable]    Script Date: 04/23/2011 19:11:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[fTable](

	[p2k] [int] NOT NULL,

	[amt] [money] NOT NULL

) ON [PRIMARY]

GO

INSERT [dbo].[fTable] ([p2k], [amt]) VALUES (1, 100.0000)

INSERT [dbo].[fTable] ([p2k], [amt]) VALUES (2, 50.0000)

INSERT [dbo].[fTable] ([p2k], [amt]) VALUES (3, 1000.0000)

/****** Object:  StoredProcedure [dbo].[usp_p2]    Script Date: 04/23/2011 19:11:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_p2]

	@p1 varchar(8000)

AS

BEGIN

	SET NOCOUNT ON;

	

	DECLARE @gid2 varchar(36) = CONVERT(varchar(36), NEWID());

	

	SELECT	 CONVERT(varchar(10), t.p2k)+@gid2 AS p2k

			,t.p2l

	FROM pTable t

	WHERE CONVERT(varchar, t.p1k) IN (SELECT [Param] FROM dbo.TvfMVParamSplit(@p1, ','))

END

GO

/****** Object:  StoredProcedure [dbo].[usp_p1]    Script Date: 04/23/2011 19:11:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_p1]

AS

BEGIN

	SET NOCOUNT ON;

	

	SELECT DISTINCT	 p1k

					,p1l

	FROM pTable

END

GO

/****** Object:  StoredProcedure [dbo].[usp_main]    Script Date: 04/23/2011 19:11:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_main] 

	@p2k varchar(8000)

AS

BEGIN

	SET NOCOUNT ON;



	SELECT p2l, amt

	FROM fTable f

		INNER JOIN pTable p

			ON f.p2k = p.p2k

	WHERE CONVERT(varchar, p.p2k) IN (SELECT REPLACE([Param],RIGHT([Param],36),'') FROM dbo.TvfMVParamSplit(@p2k, ','))

END

GO