Maintainable SQL queries on a NAV db

Miklos_Hollender

Taking some further steps towards discovering Reporting Services and T-SQL, the second bump I found on the road is that it's better not to hardcode the database and company name in the query - it means we have to use EXEC and string concatenation, but that generally looks like line noise, a total mess, so we need to find a more readable, maintanable approach.

After much ](*,) and :-k and :-# what I managed to find is the following.

Can someone perhaps suggest a simpler approach? (Yes, that stuff about putting the db and the company into a setup table I already tried and I think it's better to have them as a report parameter.)

(This query takes a year and a month as a parameter and shows the total sales from Cust. Ledger Entry to the end of that month in that year and in the previous year.)

CREATE FUNCTION NaviTable 
(
    @db varchar(50),
    @company varchar(50),
    @table varchar(50),
    @alias varchar(50)
)
RETURNS varchar(200)
AS
BEGIN
	DECLARE @retval varchar(200)
        SET @retval = ' [' + @db+ '].[dbo].['+@company +'$' + @table +'] ' + @alias + ' '
        RETURN @retval
END



CREATE PROCEDURE SalesByYearMonth
   @year integer,
   @month integer,
   @company varchar(50),
   @database varchar(50)
as 
declare @prevyear integer
declare @cleThisYear varchar(200)
declare @cleLastYear varchar(200)
set @prevyear = @year-1
set @cleThisYear = dbo.NaviTable(@database, @company, 'Cust_ Ledger Entry', 'cleThisYear')
set @cleLastYear = dbo.NaviTable(@database, @company, 'Cust_ Ledger Entry', 'cleLastYear')


exec( '

select

  (select
     sum(cleThisYear.[Sales (LCY)])
   from' 
     + @cleThisYear  + '
  where
   
     datepart(yyyy, cleThisYear.[Posting Date])= ' + @year +'
     and datepart(month,cleThisYear.[Posting Date])<=' + @month + ' ) as SalesThisYear,

  (select
     sum(cleLastYear.[Sales (LCY)])
  from' 
     + @cleLastYear  + '
  where 
  
     datepart(yyyy, cleLastYear.[Posting Date])=' + @prevyear +'
     and datepart(month,cleLastYear.[Posting Date])<=' + @month+ ') as SalesLastYear

') -- end of exec