SQL Server 2008 - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Server 2008

Author

Topic

aggaayathri
Starting Member

1 Post

Posted - 2011-06-13 : 01:49:43

How to combine the results of the stored procedure to the select statement? or I want to make the below result sets into a single result set??
i will attach my code below... plz help me out...

--Stored procedure:

DECLARE @WorkCenterGroup VARCHAR(MAX)
DECLARE @WorkCenter TABLE ([Work Center Group Code] varchar(50))

INSERT @WorkCenter ([Work Center Group Code])
SELECT DISTINCT rl.[Work Center Group Code]
FROM [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Prod_ Order Routing Line] rl where rl.[Prod_ Order No_]='101025'

SELECT @WorkCenterGroup = COALESCE(@WorkCenterGroup + ',[', '[') +
[Work Center Group Code] + ']'
FROM @WorkCenter t

declare @pivot varchar(max), @sql varchar(max)

select @sql=
'SELECT * FROM
(
Select rl.[Work Center Group Code],rl.[Prod_ Order No_],cl.[Posting Date],oc.[Description]
from [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Prod_ Order Routing Line] rl
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Capacity Ledger Entry] cl on rl.[Prod_ Order No_]=cl.[Prod_ Order No_]
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Prod_ Order Component] oc on oc.[Prod_ Order No_]=rl.[Prod_ Order No_]
where rl.[Routing Status]=3 and rl.[Prod_ Order No_]=101025
)t
PIVOT (max([Posting Date]) FOR [Work Center Group Code]
IN ('+@WorkCenterGroup+')) AS pvt'

exec(@sql)

---Select Statement

Select
distinct
rl.[Work Center Group Code],
h.[Sell-to Customer Name]as [Customer Name],
l.[Promised Delivery Date]as [Delivery Date],
l.[Document No_] as [Order No],
l.No_ as [Item Code],
oc.[Description],
cl.[Posting Date],
po.[Machine Number],
l.[Currency Code] as Currency,
l.[Unit Price] as Price,
l.[Line Amount]as [Total Value],
rl.[Prod_ Order No_]

from [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Sales Header]h
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Sales Line] l on h.No_=l.[Document No_]
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Production Order] po on po.[No_]=l.[Shortcut Dimension 2 Code]
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Prod_ Order Component]oc on po.[No_]=oc.[Prod_ Order No_]
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Prod_ Order Routing Line] rl on oc.[Prod_ Order No_]=rl.[Prod_ Order No_]
inner join [CC Tools_IN_2009_SP1_Dev].[dbo].[CRONUS India Ltd_$Capacity Ledger Entry] cl on rl.[Prod_ Order No_]=cl.[Prod_ Order No_]
where l.[Document Type]=1 and l.[Type]=2 and l.[Shortcut Dimension 2 Code]is not null and po.[Status]=3 and rl.[Prod_ Order No_]=101025

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-13 : 10:30:30

What do you mean by "combine the results"? Did you want to JOIN the two resultsets?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources