Unused Params & Variables

[blackshirt]

In SSMS I generated a script of all my SPs on the database. I then brought that script in and highlighted them all then selected SQL Refactor -> Find Unused Variables and Parameters........I get an error message saying it cant highlight some variables because of some syntax errors while being parsed. So then I started from beginning and highlighted SP1 and no issues. Then highlighted SP1 and SP2.....again no issues.....then I did SP1, SP2, and SP3 then got the error....so I just highlighted SP3 and again no issues.....so is there a limit, buffer limit, or something wrong with trying to do this against multiple SP's in your database?

My main goal is to try to run this utility against all my SP's in my DB and not just one by one which would take forever.

thanks

[Anu Deshpande]

Many thanks for your post and apologies for inconvenience caused.

Are you able to parse the same multiple SP query through management Studio 'Parse' functionality?

As when I open multiple SP's in one Query Window and first parse it it gives me error: "Msg 154, Level 15, State 1, Procedure spArtikelBuchung, Line 240
a USE database statement is not allowed in a procedure, function or trigger."

But SQL Refactor do the job for me.

Kindly let me know the detailed version of SQL Refactor you have installed on your machine.

You can know the detailed version of SQL Refactor by navigating to SQL Refactor --> About SQL Refactor and version number is displayed on bottom right.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com

[blackshirt]

I am using SQL Refactor 1.5.1.83.......

When I hightlight the 3 SP's and press the "Parse" button it says "Command(s) completed successfully".......when I do that with "Find Unused variables and parameters".....I get the error in my original message.

Here is an example SP...........

CREATE PROCEDURE [Test].[procTest]
(
@Id uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int

SELECT [Id]
,[temId]
,[ActivityId]
,[AAmount]
FROM [test].[test1]
WHERE ([Id] = @Id)


IF @@Error = 0
BEGIN
IF @@RowCount = 0
BEGIN
RETURN 1
END
END

RETURN @@ERROR

END
GO

[Joojooflop]

I have the same issue. I still get the error if I strip down blackshirt's proc to this:

[mnowill]

I am having the same issue and can reproduce the the same situations as Joojooflop

[Joojooflop]

This appears to have been fixed in SQL Prompt 5.

[frisky]