CODEWITHSUNDEEP
sql-serverdelphistored-proceduresparameters
Shaun
Shaun

Reputation: 73

Passing SQL Server stored procedure parameter names via Delphi

I'm new to Delphi and trying to find my way around calling some stored procedures in SQL Server.

This is the code I have been working with at the moment and it works....

FConnection := TADOConnection.Create(nil);
FMetaDataSP := TADOStoredProc.Create(nil);
LoadDBSettings;

FMetaDataSP.Connection := FConnection;
FMetaDataSP.ProcedureName := 'Messaging.ListMessageSections';
FMetaDataSP.Parameters.CreateParameter('@ReferralID', ftInteger, pdInput, 4, null);
FMetaDataSP.Parameters.CreateParameter('@ConsumerID', ftInteger, pdInput, 4, null);

Dataset := FMetaDataSP;
FMetaDataSP.Parameters.ParamByName('@ReferralID').Value := ReferralID;
FMetaDataSP.Parameters.ParamByName('@ConsumerID').Value := ConsumerID;

.... but when I do a profile in SQL Server I see this is the SQL that is executed.

exec Messaging.ListMessageSections 1,1

What I want is this instead...

exec Messaging.ListMessageSections @ReferralID=1, @ConsumerID=1

so that the ordering of the parameters in the code base are not important.

Is this possible?

Upvotes: 2

Views: 4636

Answers (1)

crefird
crefird

Reputation: 1610

You can do what you want if you use TADOQuery instead of TADOStoredProc.

FMetaDataSP := TADOQuery.Create(nil);
FMetaDataSP.Connection := FConnection;
FMetaDataSP.SQL.Text := 'Messaging.ListMessageSections @ReferralID=:ReferralID, @ConsumerID=:ConsumerID';

FMetaDataSP.Parameters.ParamByName('ReferralID').Value := ReferralID;
FMetaDataSP.Parameters.ParamByName('ConsumerID').Value := ConsumerID;
FMetaDataSP.ExecSQL;

Upvotes: 2

Related Questions