Page 1 of 1
Possible select the record from store procedure
Posted: Thu 02 Apr 2015 04:39
by 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!
Re: Possible select the record from store procedure
Posted: Fri 03 Apr 2015 13:32
by 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;
Re: Possible select the record from store procedure
Posted: Wed 08 Apr 2015 03:35
by 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?
Re: Possible select the record from store procedure
Posted: Fri 10 Apr 2015 12:43
by 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