Skip to Content
0
Former Member
Oct 29, 2012 at 01:09 PM

Problem with variables/prompting in query

26 Views

I seem to be having a problem with this query but only one portion. The problem stems from the user prompts in the beginning of the query. It seems to work just fine until I add one thing to it.

--Parameter for excise taxable

DECLARE @exciseTaxable char(1)

SET @exciseTaxable = ( select top(1) i.QryGroup3 from oitm i where i.QryGroup3 = '[%2]' )

--Parameters for dates

DECLARE @StartDate DATETIME

SET @StartDate = /* select min(j1.RefDate) from ojdt j1 where j1.RefDate >= */ '[%0]'

DECLARE @EndDate DATETIME

SET @EndDate = /* select max(j2.RefDate) from ojdt j2 where j2.RefDate <= */ '[%1]

The query works with these 3 already. But as soon as I add this:

DECLARE @mdetExempt char(1)

SET @mdetExempt = ( select top(1) c.QryGroup26 from ocrd c where c.QryGroup26 = '[%3]' )

It seems to blow up. Giving me a problem with 'Statement Service Contracts OCTR could not be prepared.' Along with that if I put the parameter exciseTable below the date parameters it blows up as well or if I put the exciseTable parameter query within comments it blows up as well with the same error as before. Any ideas?