Query with parameters

vdev · Post by **vdev** » Tue 15 Sep 2009 08:34

Hi Devart,

I downloaded the trial edition and I see that queries with parameters are executed with sp_executesql.

This is okay so far as I get the rows affected from my query. If however I want the scope_identity() it doesn't work because sp_executesql is executed under its own context.

Is it possible to somehow embed the parameter values in the query before it is sent to mssql?

Dimon · Post by **Dimon** » Tue 15 Sep 2009 13:12

SDAC doesn't use sp_executesql to execute a query with parameters. To assign a parameter value, you can use the followng code:

Code: Select all

  MSQuery.ParamByName(paramname).Value := Value;

vdev · Post by **vdev** » Wed 16 Sep 2009 06:22

I know that you don't explictly use sp_executesql but oledb does. If you execute a query or an insert/update statement, check the mssql profiler and you will see that sp_executesql is used.

This is because parameters are sent to the back end to be evaluated by SQL.

Dimon · Post by **Dimon** » Wed 16 Sep 2009 07:11

Was the problem solved? If any other questions come up, please contact me.

vdev · Post by **vdev** » Tue 27 Jul 2010 10:05

Dimon wrote:Was the problem solved? If any other questions come up, please contact me.

No I still have the problem.

The problem is that if a query is without parameters, it is executes "as-is".

If the query has parameters then I see in the profiler that sp_executesql is used. I'd like parameters to be embedded in the query before being sent to the server.

vdev · Post by **vdev** » Tue 27 Jul 2010 10:09

Following up, here's what I get from the profiler.

Without parameters
SQL:BatchStarting select * from order
SQL:BatchCompleted select * from order

With parameters
exec sp_executesql N'select * from order where id=@P1',N'@P1 int',1
[/b]

What i need is that when I have parameters, the first scenario (select * from order where id=1) is used instead of calling stored procedure and letting sql handle parameters.

The previous engine (sqldirect) I use works like expected.

Dimon · Post by **Dimon** » Tue 27 Jul 2010 14:19

To include parameters values directly in a SQL query you should use macros instead of parameters. You can find more detailed information about macros in the SDAC help. But using of macros in this case is not a better way.
Please give a detailed description of the problem with using scope_identity().

vdev · Post by **vdev** » Wed 28 Jul 2010 10:21

Dimon wrote:To include parameters values directly in a SQL query you should use macros instead of parameters. You can find more detailed information about macros in the SDAC help. But using of macros in this case is not a better way.
Please give a detailed description of the problem with using scope_identity().

Hi, what do you mean is not a better way?

About scope_identity, the problem is that when sp_executesql is used, then your insert statement is executed by sp_executesql. If after the sp_executesql you run select scope_identity() you will see that you will not get the last identity value that was executed by sp_executesql because the scope of the statement is different.

I urge you to find a way to embed parameters the performance impact is killing me.

Dimon · Post by **Dimon** » Wed 28 Jul 2010 10:38

1. When you use parameters then SDAC passes it to SQL Server in optimal format and in most cases it improves performance.

2. The point is that the SCOPE_IDENTITY function returns the last identity value only in the same scope.
Therefore to solve the problem use the following SQL to insert the record:

Code: Select all

  INSERT INTO ... VALUES ...;
  SET :Identity_field = SCOPE_IDENTITY()