I have two tables
table 1 is
SELECT * FROM dbo.TBL_WD_WEB_DECK
WD_ID WD_TITLE
------------------
1 2
and data in 2nd table is
WS_ID WS_WEBPAGE_ID WS_SPONSORS_ID WS_STATUS
-----------------------------------------------------
1 1 1,2,3,4 Y
I wrote the following stored procedure to insert the data into both the tables catching the identity of main table dbo.TBL_WD_WEB_DECK. WD_ID is related with WS_WEBPAGE_ID. I wrote the cursor in update action to split the WS_SPONSORS_ID column calling the split function in the cursor. But it is not working.
The stored procedure is:
ALTER procedure [dbo].[SP_Example_SPLIT]
(
@action char(1),
@wd_id int out,
@wd_title varchar(50),
@ws_webpage_id int,
@ws_sponsors_id varchar(250),
@ws_status char(1)
)
as
begin
set nocount on
IF (@action = 'A')
BEGIN
INSERT INTO dbo.TBL_WD_WEB_DECK(WD_TITLE)VALUES(@WD_TITLE)
DECLARE @X INT
SET @X = @@IDENTITY
INSERT INTO dbo.TBL_WD_SPONSORS(WS_WEBPAGE_ID,WS_SPONSORS_ID,WS_STATUS)
VALUES (@ws_webpage_id,@ws_sponsors_id,@ws_status)
END
ELSE IF (@action = 'U')
BEGIN
UPDATE
dbo.TBL_WD_WEB_DECK
SET
WD_TITLE = @wd_title WHERE WD_ID = @WD_ID
UPDATE dbo.TBL_WD_SPONSORS
SET WS_STATUS = 'N'
where WS_SPONSORS_ID not in (@ws_sponsors_id) and ws_webpage_id = @wd_id
BEGIN
/* Declaring Cursor to split the value in ws_sponsors_id column */
Declare @var int
Declare splt cursor for
/* used the split function and calling the parameter in that split function */
select * from iter_simple_intlist_to_tbl(@WS_SPONSORS_ID)
OPEN splt
FETCH NEXT FROM splt INTO @var
WHILE (@@FETCH_STATUS = 0)
begin
if not exists(select * from dbo.TBL_WD_SPONSORS where WS_WEBPAGE_ID = @wd_id
and WS_SPONSORS_ID = @var)
begin
insert into
dbo.TBL_WD_SPONSORS (WS_WEBPAGE_ID,WS_SPONSORS_ID) values(@wd_id,@var)
end
end
CLOSE SPONSOR
DEALLOCATE SPONSOR
END
END
END
The result I want is if I insert the data in WD_ID and IN WS_SPONSORS_ID column the data in the WS_SPONSORS_ID column should split and I need to compare it with WD_ID. The result I need is:
WD_ID WD_TITLE
---------------------
1 TEST
2 TEST1
3
WS_ID WS_WEBPAGE_ID WS_SPONSORS_ID WS_STATUS
--------------------------------------------------------
1 1 1 Y
2 1 2 N
3 1 3 Y
If I pass the string in WS_SPONSORS_ID as 1,2,3 it has to split like the above. Can you help?
