SSRS and SQL and NAV Passed Company Variable as Paramter

pettal

Hi All,

I have created a SQL Function which basically translates the NAV Flowfields into the correct SQL and returns the decimal value. All good, but I tried to paramaterise the Company Name and pass this through the function and it doesn't like it and I can't figure out why.

Currently it works like this and this does work:
Within SSRS I have a SQL Text Query and within that a call to my function:
(Select dbo.ReturnNAVFlowFields('Purchases Cost',CA.[Client No_] ,CA.[Asset Code],@StartDate,@EndDate))

The function looks like this:
IF @FlowField LIKE 'Purchases Cost' BEGIN
SET @Amount = (SELECT ISNULL(SUM([Amount (LCY)]), 0)
FROM [CompanyName$Asset Ledger Entry]
WHERE [Client No_] LIKE @ClientNo
AND [Asset Code] LIKE @AssetCode
AND [Transaction Type] = 0
AND [Posting Date] IS NOT NULL
AND [Posting Date] > @StartDate
AND [Posting Date] < @EndDate)
END

Which works lovely if I only have 1 company... I dont, I have 4 and I don't want to have to repeat code, sql or functions to do it. ](*,)

Any pointers to paramaterise the company name would be hugely appreciated!

ara3n

You can use

SET @sql = 'SELECT * FROM ' + @tablename 
EXEC(@sql) -- execure the query

The other option as you mentioned is to have a case statement and list all the companies queries repeated.

pettal

Thanks for the response.

I did look at the first option you gave of using a dynamic SQL query within the function or even re-writing as a stored proc but had difficulty calling the dynamic query from within the report. The error it gave intimated that I would be better using a SP from within the report at which point I figured I might as well write the whole thing as an SP in the first place!

Ok, thanks for your help I will go back to the drawing board. I thought there might have been a simpler solution that I was just missing but it appears I may as well go back and start again. :roll:

Thanks for your help!