Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Replace sqlserver call executesql with FireDac, how to define params?


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 1 - Last Post: Jul 15, 2017 3:16 AM Last Post By: Dmitry Arefiev
William Egge

Posts: 98
Registered: 11/29/06
Replace sqlserver call executesql with FireDac, how to define params?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 13, 2017 9:50 PM
I am using SQL Server and I am building a test rig for dynamic sql using FireDAC. The production code will use executesql on the below SQL, which is stored in a database table, but for testing I do not want to wrap this inside executesql and I want to call it using FireDac.

The SQL contains parameters, and so far I have not been able to find a way to pass in parameters. I just get an error back from SQL server that says @whatever parameter is required.

Here is my SQL code.

;with xmlnamespaces (DEFAULT 'http://fwdco.com/api')
select
  @Changes = (
    select
      InstanceId,
      clientId,
      employeeId,
      case
        when EEts is null then 'C'
      else
        'U'
      end as CRUD
    from
      (
        select
          EV.InstanceId,
          EV.clientId,
          EV.employeeId,
          Max(EV.ts) as LastEventTs,
          Max(E.ts) as EEts
        from
          api.SubscriptionEvents EV
          left outer join api.Employee E on
          (
            (E.InstanceId = EV.InstanceId)
            and (E.clientId = EV.clientId)
            and (E.employeeId = EV.employeeId)
          )
          
        where
          (EV.InstanceId = @InstanceId)
          and (EV.eventId >= @SinceEventId)
          and (EV.schemaName = 'Employee')
          and (EV.clientId is not null)
          and (EV.employeeId is not null)
          and (E.employeeId is null) or (E.ts < EV.ts)
        group by
          EV.InstanceId,
          EV.clientId,
          EV.employeeId
        
      ) Change
    for xml path ('req'), TYPE, ELEMENTS XSINIL, ROOT('getEmployee')
  )
  option(recompile)
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Replace sqlserver call executesql with FireDac, how to define params?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 15, 2017 3:16 AM   in response to: William Egge in response to: William Egge
FireDAC uses ":name" as parameter markers.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02