locked
Sql Server RRS feed

  • Question

  • 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
    [blue][/blue][blue][/blue]
    Monday, June 13, 2011 5:58 AM