Skip to Content
0

is there PlanView like tool for Stored Procedure

Feb 12, 2017 at 08:58 AM

58

avatar image
Former Member
BEGIN
DECLARE VAR NVARCHAR(7);
VAR = '201507'
VAR = LEFT(:VAR,4)||'0'||RIGHT(:VAR,2);
END

Does the above code cause a cyclic dependency

or is below code - a better way for performance / paralellization

BEGIN
DECLARE VAR NVARCHAR(7);
VAR = '201507'
V_VAR = LEFT(:VAR,4)||'0'||RIGHT(:VAR,2);
END

Finally what is a tool or way to check for performance of Stored Procedures . The Planview option does not work for Stored Procedures

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Feb 12, 2017 at 12:25 PM
0

Why the first code should get into a cylcic dependency? The expressions are evaluated and the result is then assigned to variable var. There will be no cyclic dependency.

The PlanViz tool can be used for stored procedures too (although for anonymous blocks). You will get information about statement statistics and the plan.

Regards,
Florian

Show 5 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Florian : This anonymous block is so cool and will come very handy . I can test small parts of a large stored procedure incrementally by this .

As for the PlanView for the anonymous block , I could not make it work for the same snippet

DO
BEGIN
DECLARE VAR NVARCHAR(7);
VAR = '201507';
VAR = LEFT(:VAR,4)||'0'||RIGHT(:VAR,2);
SELECT :VAR VAR FROM DUMMY;
END;

The anonymous block works , but not sure how to execute the PlanView .

0

Im not sure what you mean with "PlanView". Do you mean the PlanViz tool or the Explain Plan functionality?

0
Former Member
Florian Pfeffer

YEs the PlanViz too that we use to check the working and performance of calculation views

0
Former Member

Also I was noticing that I cant seem to use the SQL Debugger in Anonymous block , is that correct ?

0

Right, no debugger support.

0