0

I am trying to pass a @year parameter through SSRS to a SQL Server stored procedure that queries a Progress database. I am getting a syntax error at the @year value when the stored procedure runs in ssrs.

Can anyone see something blindingly obvious that I have missed out please? 

[stored procedure name]
    @year nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(max)
    set @SQL = 'select * 
                from openquery
                     ("server name",
                        ''select 
                             "PLACE-REF", "ACCOUNT-CODE", "TRANS-YEAR" 
                          from "PLACE"."LOCATION" 
                          where ((LTRIM(RTRIM(("LOCATION"."ACCOUNT-CODE" ))) IN (''''S/RTBQ'''',''''S/VATC'''' )))
                            and ("LOCATION"."TRANS-YEAR" =  ''''' + @year + ''''') '') '

    --print @sql
    EXEC (@SQL)
END

Obviously this is a snippet of the code, but it gives a flavour of where I am at with reference to the parameter.

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3735855
  • 144
  • 2
  • 20
  • 1
    Can you provide the result of the 'print @sql' command? – Juan Mar 12 '15 at 13:38
  • Juan, thankyou, I ran the print @sql and spotted immediately where the error was, and pasted the whole address in for the "trans-year" part and bingo it ran straight through. – user3735855 Mar 12 '15 at 13:44

0 Answers0