Thanks for your kind reply,I got the solution with the help of my senior colleague.I would like to share this Stored Procedure so that it may be helpful for someone.We have to make sure that we pass PassType values from the from the end as (VIP,10,1000/General,100,500/Children,100,200) as one string to the sp parameter @PassTypeListValues,where VIP is PassType,10 is Quantity and 1000 is the Price.Believe me these 2 sps will be very useful/helpful for those who are having this kind of scenario/functionality.
USE [EMS]
GO
/****** Object: StoredProcedure [dbo].[SPINSERTEVENTDETAILS_New] Script Date: 08/18/2012 16:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPINSERTEVENTDETAILS_New]
@EventID NVARCHAR(10),
@EventTitle NVARCHAR(50)=NULL,
@EventDescription NVARCHAR(MAX)=NULL,
@StartDate DATE=NULL,
@EndDate DATE=NULL,
@TimeFrom NVARCHAR(50)=NULL,
@TimeTo NVARCHAR(50)=NULL,
@Location NVARCHAR(100)=NULL,
@EventType NVARCHAR(50)=NULL,
@EventPaymentType NVARCHAR(50)=NULL,
@EventURL NVARCHAR(50)=NULL,
@EventBrochure IMAGE=NULL,
@PublishDate DATETIME=NULL,
@RemovedPublishDate DATETIME=NULL,
@EventOrganisorName NVARCHAR(50)=NULL,
@CreatedBy NVARCHAR(50)=NULL,
@PassTypeListValues NVARCHAR(max)=NULL
AS
BEGIN
INSERT INTO tbl_EventDetails
(EventID,EventTitle,EventDescription,StartDate,EndDate,TimeFrom,TimeTo,Location,EventType,EventPaymentType,EventURL,
EventBrochure,PublishDate,RemovedPublishDate,EventOrganisorName,CreatedBy,CreatedDate)
values
(@EventID,@EventTitle,@EventDescription,@StartDate,@EndDate,@TimeFrom,@TimeTo,@Location,@EventType,@EventPaymentType,@EventURL,
@EventBrochure,@PublishDate,@RemovedPublishDate,@EventOrganisorName,@CreatedBy,GETDATE())
Declare @EventIDInserted int
select @EventIDInserted=SCOPE_IDENTITY()
--ForPassLitofValues
DECLARE @tblPassTypeListValues AS TABLE(ID INT IDENTITY(1,1),DATA NVARCHAR(max))
DECLARE @COUNT INT
DECLARE @I INT
DECLARE @DATA NVARCHAR(100)
INSERT INTO @tblPassTypeListValues
EXEC [SPSplitString] @PassTypeListValues,'/'
SELECT @I=1, @COUNT=COUNT(*) FROM @tblPassTypeListValues
WHILE(@I<=@COUNT)
BEGIN
SELECT @DATA=DATA FROM @tblPassTypeListValues WHERE ID=@I
if @DATA <>''
---Now Loop Data Values to get Comma Seperated Values
DECLARE @tblPassTypeListValues2 AS TABLE(ID INT IDENTITY(1,1),DATAType NVARCHAR(max))
DECLARE @COUNT2 INT
DECLARE @I2 INT
DECLARE @DATAType NVARCHAR(50),@DATAQuantity NVARCHAR(50),@DATAPrice NVARCHAR(50)
DECLARE @EventTyp NVARCHAR(255)
DECLARE @pos INT
DEClare @Quanity NVARCHAR(255)
DECLARE @pos2 INT
Declare @DATA2 NVARCHAR(255)
Declare @Price NVARCHAR(255)
-- WHILE CHARINDEX(',', @DATA) > 0
--BEGIN
print @DATA;
SELECT @pos = CHARINDEX(',', @DATA)
--print @pos;
SELECT @EventTyp = SUBSTRING(@DATA, 1, @pos-1)
--print @EventTyp;
SELECT @DATA2 = SUBSTRING(@DATA, @pos+1, LEN(@DATA)-@pos)
--print @DATA2;
SELECT @pos2 = CHARINDEX(',', @DATA2)
--print @pos2;
SELECT @Quanity = SUBSTRING(@DATA2, 1, @pos2-1)
--print @Quanity;
SELECT @Price = SUBSTRING(@DATA2, @pos2+1, LEN(@DATA2)-@pos2)
--print @Price;
INSERT INTO tbl_PassTypeCategory
(EventID,PassType,Quantity,Price,CreatedBy,CreatedDate)
VALUES
(@EventIDInserted,@EventTyp,@Quanity,@Price, @CreatedBy,GETDATE())
set @I=@I+1
END
end
USE [EMS]
GO
/****** Object: StoredProcedure [dbo].[SPSplitString] Script Date: 08/18/2012 18:05:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPSplitString]
(
@StrList NVARCHAR(MAX)= NULL,
@SplitChar varchar(2) = ';'
)
AS
--Declaring variables
DECLARE @Data NVARCHAR(100),@Pos INT
DECLARE @Tbl AS TABLE(Data varchar(100))
SET @StrList=LTRIM(RTRIM(@StrList))+@SplitChar
SET @Pos=CHARINDEX(@SplitChar,@StrList,1)
IF REPLACE(@StrList,@SplitChar,'')<>''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Data=LTRIM(RTRIM(LEFT(@StrList,@Pos-1)))
INSERT INTO @Tbl VALUES(@Data)
SET @StrList=RIGHT(@StrList,LEN(@StrList)-@Pos)
SET @Pos=CHARINDEX(@SplitChar,@StrList,1)
END
END
SELECT * FROM @Tbl
