Possible select the record from store procedure

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Possible select the record from store procedure

Post by tcflam » Thu 02 Apr 2015 04:39

Hi,

I want to improve the sql performance because the application is running in different countries. Can it possible to select the records from store procedure with dynamic conditions? If it can, then the data result will not return too many records. Currently, I create the view in SQL server. And then I use TMSQuery to do the filtering in client side. The performance should be poor than running in server side first.

Thanks!

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Possible select the record from store procedure

Post by azyk » Fri 03 Apr 2015 13:32

Yes, you can write a SELECT query in a stored procedure, and pass dynamic conditions to a procedure as parameters. To retrieve records, execute this stored procedure in the TMSStoredProc dataset. For example:

Code: Select all

MSStoredProc.StoredProcName := 'sp_name';
MSStoredProc.ParamByName('Condition1').Value := Value1;
MSStoredProc.ParamByName('Condition2').Value := Value2;
MSStoredProc.Open;

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: Possible select the record from store procedure

Post by tcflam » Wed 08 Apr 2015 03:35

Hi Azyk,

Sorry! Your sample is a basic one-pass condition. I want to have two pass condition. First is passing parameter value to stored procedure. Then pass the second level parameter in stored procedure result. For example:

select * from exec 'sp_name'
where condition1

Is it possible?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Possible select the record from store procedure

Post by azyk » Fri 10 Apr 2015 12:43

To solve the problem, you can use a T-SQL function OPENQUERY. The SQL query will look as the following:

Code: Select all

select * from   
openquery([server_name\instance_name], 'exec database_name.dbo.sp_name')
where field_name = value
You can execute the generated SQL query in a TMSQuery dataset.

See more details about OPENQUERY in the SQL Server documentation: https://msdn.microsoft.com/en-us/library/ms188427.aspx and in the MSDN blog: https://social.msdn.microsoft.com/Forum ... cal-server

Post Reply