Possible select the record from store procedure

[tcflam]

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]

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]

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]

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