Using Lazarus 2.04 and running against a 2014 MS SQL server, I have a stored procedure with this signature:Try
CREATE PROCEDURE [dbo].[lst_UserNotifications] ( @chrUserID VARCHAR(16), @bitShowComplete bit = 0, @bitShowIgnored bit = 0, @bitIgnoreYourOwn bit = 0 )
Using a
- TMSSQLConnection
- TSQLQuery
- TSQLTransaction
and executingworks fine and delivers the expected result set.
SQLQuery.SQL.Text := 'EXEC lst_UserNotifications ''NHTACCESS'', 0, 0, 0'; query.Active := True;
I have had no success using proper parameters no matter what I tried:
With query.ParseSQL = True and no parameter objects
Creating Parameter object via IDE
Specifying the 4 required parameters as in EXEC lst_UserNotifications :chrUserID, bitShowComplete ...' or not and any imaginable combination...
What do I need to do?
Thanks for any help you can give!
The first one works fine as expected since it's just a replacement of literals with variables from my working example.Maybe:
The second one fails as described in my initial post:
Parameter "chrUserID" not found
Nope.Different engines demand different solutions.
Made a little progress, though.
The crucial line isso that the parameter objects get built. I had wrongfully assumed that ParseSQL would do that. Once I had that, the 'Parameter not found' error went away.
query.ParamCheck := True;
Now another issue emerged:
If i usenothing gets returned.
query.ExecSQL;
If I useI get the proper column headers showing up in my attached grid, but no data ...
query.Active := True;