Store procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Store procedure help

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2012-08-08 : 07:02:43

i have a table Companiesdata

CREATE TABLE [dbo].[Companiesdata]
(
[Company Name] nvarchar(255),
[Industry] varchar(40),
[ParentId] int NULL,
)

the records are

CompanyName Industry Parent ID

Xyz technologies Software 1
apple Technologies software 1
Sun network media 2
abc Technologies advertising 4
PQR Technnologies Marketing 5
abc Technologies Media 4

i have other table

create table dbo.companiesss
(
autoid int identity(1,1),
companyname varchar(max),
Industry varchar(max)
)

i wrote a procedure as below:

create proc pr_getlistofcompaniesss (@tparentid varchar(20))
as
begin

insert into dbo.companiesss(companyname,industry)

select [CompanyName],[Industry] from [Companiesdata]
where parentid in(select items from dbo.split(@tparentid,','))

except
select company name,industry from dbo.companiesss

end

The output is as below:

pr_getlistofcompaniesss 1,2,4

the records are displayed as

AutoID Company name Industry

1 apple Technologies software
2 Sun network Media
3 xyz Technologies software
4 abc Technologies advertising
5 abc technologies media

instead my output should be as below:

pr_getlistofcompaniesss 1,2,4

AutoID Company name Industry

1 apple Technologies software
2 Sun network Media
3 xyz Technologies software
4 abc Technologies advertising,media

i.e if i have the same company(Here abc technologies) with different industries name, then the industry field should be seperated with comma displaying the record on same row i.e ( advertising,media)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-08 : 08:02:08

CREATE TABLE [dbo].[Companiesdata]
(
[Company Name] NVARCHAR(255),
[Industry] VARCHAR(40),
[ParentId] INT NULL,
)

INSERT INTO [dbo].[Companiesdata]
SELECT 'Xyz', 'technologies Software', 1
UNION ALL
SELECT 'apple', 'Technologies software', 1
UNION ALL
SELECT 'Sun', 'network media', 2
UNION ALL
SELECT 'abc', 'Technologies advertising', 4
UNION ALL
SELECT 'PQR', 'Technnologies Marketing', 5
UNION ALL
SELECT 'abc', 'Technologies Media', 4

CREATE TABLE dbo.companiesss
(
autoid INT IDENTITY(1, 1),
companyname VARCHAR(MAX),
Industry VARCHAR(MAX)
)

CREATE PROC pr_getlistofcompaniesss ( @tparentid VARCHAR(20) )
AS
BEGIN

INSERT INTO dbo.companiesss
( companyname, industry )
SELECT DISTINCT
cd.[Company Name],
SUBSTRING(( SELECT ', ' + I.[Industry]
FROM [dbo].[Companiesdata] i
WHERE cd.[Company Name] = I.[Company Name]
FOR
XML PATH('') ), 2, 8000) AS CompanyName
FROM [dbo].[Companiesdata] cd
WHERE parentid IN ( SELECT *
FROM dbo.split(@tparentid) )
EXCEPT
SELECT [companyname], industry
FROM dbo.companiesss

END

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 09:51:11

the way its written i think you should be calling proc as

pr_getlistofcompaniesss '1,2,4'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tsaliki
Starting Member

19 Posts

Posted - 2012-08-09 : 01:36:21

@lionofdezert : Thank you for your response,But i am getting the following error.Can you please check and if possible can u help me ?

Msg 116, Level 16, State 1, Procedure pr_getlistofcompaniesss, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 10:08:17

[code]
CREATE PROC pr_getlistofcompaniesss ( @tparentid VARCHAR(20) )
AS
BEGIN

;With New_Companies
AS
(
select [CompanyName],[Industry] from [Companiesdata]
where parentid in(select items from dbo.split(@tparentid,','))

except

select [company name],industry from dbo.companiesss

)

INSERT INTO dbo.companiesss
( companyname, industry )
SELECT c.[companyname],
STUFF((SELECT ',' + Industry
FROM New_Companies
WHERE CompanyName = c.CompanyName
FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT [CompanyName] FROM New_Companies) c
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources