Är nu nästan klar med min SP. Har dock ett problem jag inte hittar en lösning på. Den hittar inte kolumn 'o' som är en alias. Felmeddelandet jag får är "The column prefix 'o' does not match with a table name or alias name used in the query."

<code>
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GetC1FakturaMoogioTotalSP]
(
@SupplierID int,
@Datum1 datetime,
@Datum2 datetime,
@Status nvarchar(20),
@TypeOfCustomer int,
@ButikID Varchar(50),
@Saljare int,
@TypeOfOrder int,
@Moms decimal
)
As

Declare @SelectClause Varchar(8000),@FromClause varchar(8000),@WhereClause varchar(8000)

Set @SelectClause = 'Select (Sum(CUSTOMOrders.OrderSum)/1.25) As TotTotExl, ((Sum(CUSTOMOrders.OrderSum)/1.25)-Sum(CUSTOMOrders.OrderCostPriceSum)) As TotMargkr, ((Sum(CUSTOMOrders.OrderSum)/1.25-Sum(CUSTOMOrders.OrderCostPriceSum))/(Sum(CUSTOMOrders.OrderSum)/1.25)*100) As TotMargpr, Count(OrderID) As TotAntal, ((Sum(CUSTOMOrders.OrderSum)/1.25)/Count(OrderID)) As TotGenom'

Set @FromClause = ' From CUSTOMOrders, BCCustomers, Units, Users'
If(@SupplierID <> -1)
If(@SupplierID = 1)
Set @FromClause = @FromClause + ', CUSTOMOrderArticles, CUSTOMArticles'
Else If(@SupplierID = 2)
Set @FromClause = @FromClause + ', CUSTOMUnspecifiedArticles'
Else
Set @FromClause = @FromClause + ', CUSTOMOrderSpares, CUSTOMFolderTypes, CUSTOMSubFolders, CUSTOMFolders'
Set @FromClause = @FromClause + ' JOIN dbo.SplitOrderIDs(''' + @Status + ''') AS s ON CUSTOMOrders.OrderID = s.OrderID'

Set @WhereClause = ' Where (CUSTOMOrders.CreatedDate Between ''' + convert(varchar, @Datum1) + ''' And ''' + convert(varchar, @Datum2) + ''')'

If(@TypeOfCustomer<>-1)
If(@TypeOfCustomer<>0)
Set @WhereClause = @WhereClause + ' And (BCCustomers.BCUserID=CUSTOMOrders.BCUserID) And (BCCustomers.ExtraOptionStr='''''''
Else
Set @WhereClause = @WhereClause + ' And (BCCustomers.BCUserID=CUSTOMOrders.BCUserID) And (BCCustomers.ExtraOptionStr<>'''''''

If(@SupplierID <> -1)
If(@SupplierID = 1)
Set @WhereClause = @WhereClause + ' And (CUSTOMOrderArticles.OrderID=CUSTOMOrders.OrderID) And (CUSTOMOrderArticles.ArticleID=CUSTOMArticles.ArticleID) And (CUSTOMArticles.SupplierID=''' + @SupplierID + ''')'
Else If(@SupplierID = 2)
Set @WhereClause = @WhereClause + ' And (CUSTOMUnspecifiedArticles.OrderID=CUSTOMOrders.OrderID) And (CUSTOMUnspecifiedArticles.SupplierID=''' + @SupplierID + ''')'
Else
Set @WhereClause = @WhereClause + ' And (CUSTOMOrderSpares.OrderID=CUSTOMOrders.OrderID) And (CUSTOMFolderTypes.TypeID=CUSTOMOrderSpares.TypeID) And (CUSTOMFolderTypes.FolderID=CUSTOMSubFolders.FolderID) And (CUSTOMSubFolders.ParentID=CUSTOMFolders.FolderID) And (CUSTOMFolders.SupplierID=''' + @SupplierID + ''')'

If (@ButikID <> '-1')
Set @WhereClause = @WhereClause + ' And (Units.UnitID In ''' + @ButikID + ''')'

If (@Saljare <> -1)
Set @WhereClause = @WhereClause + ' And (CUSTOMOrders.CreatedByID=''' + @Saljare + ''')'

If (@TypeOfOrder != -1)
Set @WhereClause = @WhereClause + ' And (CUSTOMOrders.TypeOfOrder=''' + @TypeOfOrder + ''')'

exec (@SelectClause + @FromClause + @WhereClause)
</code>

Felet ligger i join har jag kommit fram till. Hoppas det är ngn som orkar kolla på detta. :)

Om du kan köra den och göra en PRINT på din selectvariabel

exec (@SelectClause + @FromClause + @WhereClause)

Så borde du hitta ett "o" någonstans där det inte får vara

Det är dumt att blanda tabeller med komman join och "join" join i samma select... Det ökar risken för fel.

/micke

Felet berodde på att tabellerna i from låg i fel ordning. Customorders skulle liggas sist innan man kallar på join :)