6
Does it count as using a local variable if the value originated from a parameter?
No, SQL Server will still see the reference as a parameter, which may be sniffed.
"Parameter sniffing" refers to a process whereby SQL Server's
execution environment "sniffs" the current parameter values during
compilation or recompilation, and passes it ...
3
the data in a database changes over time
This is the usual reason. While the concept of compiling a stored procedure is implementation specific, it usually involves producing a query plan for the procedure and/or the individual statements it is formed from, and storing it for later reuse (to save that part of the process being repeated every time the ...
3
The optimizer uses the sniffed parameter values regardless of your procedural logic. Imagine your proc code with only the DML statements and the parameters in it.
So there is a risk that when the plan is generated, the values to be used for the UPDATE are nonsense values when @IsUpdateA = 0. From a performance perspective, that is. Think of the WHERE clause ...
2
With your example, you can run
CREATE PROCEDURE procedure_name()
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE _accoujntno INTEGER DEFAULT 0;
DECLARE _uacn varchar(191) DEFAULT "";
DEClARE curaccount
CURSOR FOR
Select account_no ,user_account_number FROM TBL1 WHERE length(user_account_number)-...
2
It does affect the cardinality estimator if you use a different variable and set it using your parameter.
If you use the parameter directly, then it won't affect the cardinality estimator.
I did a quick test on the stackoverflow datbase and if you look at this, you will notice that when you use local variable, the estimated number of row is different then, ...
1
This error message is generally associated with an insert, which you don't have in your sample code. Assuming this is representative of your proc, I would guess you are using an INSERT INTO ... EXEC with the proc. Since that take the first results set, it would mean that this supplants another result set that is returned when @Load = 0.
Under that ...
1
The SQL Server JDBC Driver's source code on GitHub is a great place to track this down.
@Override
public void end(Xid xid, int flags) throws XAException {
// Called by the transaction mgr after the app closes the connection it was given from this physical
// connection
/*
* Ends the work performed on behalf of a transaction branch. The ...
1
All your stored procedure is doing is to put “select” in front of whatever is passed in. It doesn’t run anything.
But presumably you’re going to then ask something else to run whatever comes back, and that could be unsafe. If you’re wanting a very dynamic querying environment, then read what Erland Sommarskog has to say on the matter at http://www.sommarskog....
1
I've raised a bug for this. In the meantime, you can work around the issue by using dynamic SQL:
create or replace directory tmp as '/tmp';
declare
f utl_file.file_type;
begin
f := utl_file.fopen ('TMP', 'test.csv', 'w');
utl_file.put_line(f, '1,1');
utl_file.put_line(f, '2,2');
utl_file.fclose(f);
end;
/
create table t (
c1 int, c2 int
);
...
Only top voted, non community-wiki answers of a minimum length are eligible
