Hi Folks !
Any one there can help me ?
I have two tables as NEWS table & WEBSITE table
Third table is mapping table named WEBSITE_NEWS_MAP
News Table - news_id int not null(PK), news_description varchar(200), news_date datetime.
Website Table :- Website_id int not null (PK), Website_Name varchar(200), Website_Description varchar(1000)
Website_News_Map Table:- Website_News_Map_id int not null (PK), news_id int (FK), Website_id int (FK)
Now, I have an ADMIN PANEL in my website , where I use to add same news for multiple website.
I have created an stored procedure that successfully add the news into the NEWS table , but not into the Website_News_map table.
Below is the stored procedure , Iam using :-
ALTER
PROCEDURE [dbo].[test]
@news_heading
as
text,
@news_item
as
text,
@news_text
as
text,
@news_date
as
datetime,
@news_published_date
as
datetime,
@news_create_date
as
datetime,
@news_status
as
tinyint,
@news_sub_text
as
text,
@WebSite_ID
varchar(200)
AS
Declare
@news_id int
BEGIN
insert
into news
(news_heading,
news_item
,
news_text
,
news_date
,
news_published_date
,
news_create_date
,
news_status
,
news_sub_text
)
values
(@news_heading,
@news_item
,
@news_text
,
@news_date
,
@news_published_date
,
@news_create_date
,
@news_status
,
@news_sub_text
)
set
@news_id =
@@identity
Update
news
set
news_createdby = @news_id
where
news_id = @news_id
INSERT
INTO
WebSiteNews_Map
(
news_id
,
WebSite_ID
)
SELECT
news_id
,
WebSite_ID
FROM
WebSiteNews_Map
INNER
JOIN
dbo
.Split(@WebSite_ID,',')
AS A
ON WebSiteNews_Map.WebSite_ID
= A.Element
In the above code , the Split function is being used .(I have no idea how it is being used as I havent used FUNCTIONS in SQL SERVER. I have just copy/pasted it from INTERNET).
Below is the function :-
ALTER
FUNCTION [dbo].[Split]
( @vcDelimitedString
nVarChar(4000),
@vcDelimiter
nVarChar(100)
)
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
***************************************************************************/
RETURNS
@tblArray TABLE
(
ElementID
smallint
IDENTITY(1,1)
not
null
primary
key,
--Array index
Element
nVarChar(1200)
null
--Array element contents
)
AS
BEGIN
DECLARE
@siIndex
smallint,
@siStart
smallint,
@siDelSize
smallint
SET
@siDelSize =
LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE
LEN(@vcDelimitedString)
> 0
BEGIN
SET
@siIndex =
CHARINDEX(@vcDelimiter,
@vcDelimitedString)
IF
@siIndex = 0
BEGIN
INSERT
INTO @tblArray
(Element)
VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT
INTO @tblArray
(Element)
VALUES(SUBSTRING(@vcDelimitedString,
1,@siIndex
- 1))
SET
@siStart = @siIndex
+ @siDelSize
SET
@vcDelimitedString =
SUBSTRING(@vcDelimitedString,
@siStart ,
LEN(@vcDelimitedString)
- @siStart
+ 1)
END
END
RETURN
END
GO
If possible any one can help me ?
