Home » Visual StudioRSS

Stored Procedure Parameters Being Commented Out

Hello,

Some of my colleagues noticed that a handful of stored procedures had their parameters commented out by VS2010 automatically after doing a schema compare and checking in their code. Here is an example:

CREATE PROCEDURE [dbo].Employee_SaveEmployee

(

/*@employee_idint*/,

/*@foundation_userbit = null*/,

/*@foundation_adminbit = null*/,

/*@foundation_eabit = null*/,

/*@foundation_grant_managerbit = null*/,

/*@admin bit = null*/,

/*@superadminbit = null*/

)

 

AS

 

UPDATEEmployees

SETfoundation_admin = @foundation_admin,

foundation_ea = @foundation_ea,

foundation_grant_manager = @foundation_grant_manager,

foundation_user = @foundation_user,

[admin] = @admin,

superadmin = @superadmin

WHEREid = @employee_Id

 

Has anyone else seen this happen or can offer any explanation?

Thanks,

JA

 

8 Answers Found

 

Answer 1

Hello jabell,

Thanks for your post. However, I can’t reproduce the same issue as you received. Could you please offer  me some more details information? When you did schema  compare, what the target schema and what is the source schema? Did you set  anything in the Schema compare  Options? The example  you shown in the post, what is the definition script of it in the SQL Server?

I am looking forward to hear from you.

Thanks,

Vicky Song


Please remember to mark the replies as answers if they help and unmark them if they provide no help

 

Answer 2

Vicky,

This seems to happen  when we use the Schema Compare tool and choose 'Skip' on a stored  procedure. The tool is ignoring the 'Skip', and is instead modifying the Stored procedure  script by commending out the parameters  as I showed above.

 

Answer 3

Hello jabell,

Thanks for your reply, I can reproduce your issue. However, if you don’t want to compare the stored procedure, you can just ignore it. In the Schema Compare Options dialog, on Object Types tab, check the Stored Procedure.

Hopeful it helps you, let me know if you have any further concerns.

Thanks,

Vicky Song


Please remember to mark the replies as answers if they help and unmark them if they provide no help

 

Answer 4

Unfortunately, in this case it is required that Stored Procedures be included in the synchronization. However, we found that if we use 'Export to Editor' the problem doesn't manifest. 

Using 'Export to Editor' requires that we save the resulting script to a .sql file and then use 'Import Script' on schema view.

 

 

Answer 5

I am experiencing the same issue, but I have not been able to nail down the steps to reproduce it. I can say that it happens when I do a compare between DB (source) and project(target) and then choose to write changes. I know in most cases I marked few objects as 'skip' as I need to hand picked objects to be brought to the project.
 

Answer 6

Hello jabell, Rafael,

I will elaborate the steps how to reproduce the issue.

1). In the SQL Server, I have a stored procedure:

USE [Database1]

GO

CREATEPROCEDURE [dbo].[Procedure1]

      @param1 int= 0,

      @param2 int

AS

      SELECT @param1, @param2

RETURN 0

I create a database project in the Visual Studio, and on the database project I did “Import Database Objects and Settings…”

Now the database project has a stored procedure which has two parameters.

2). In the source database, I modify the stored procedure:

USE [Database2]

GO

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROCEDURE [dbo].[Procedure1]

      @param1 int= 0

AS

      SELECT @param1

RETURN 0

Note: Now the stored procedure has only one parameter.

3). Do a schema compare, the database as the source schema and the database project as the target schema. When I select the ‘Skip’ and do ‘Write Update’, the issue occurs. I have the parameter @param2 comment out.

CREATEPROCEDURE[dbo].[Procedure1]

       @param1int= 0,/*@param2 int*/

AS

       SELECT@param1,@param2

RETURN 0

If you want to synchronize your stored procedure, you just don’t select the ‘Skip’ option, and do ‘Write updates’ and the @param2 will be deleted in the database project instead of commented out. If you don’t want to synchronize your stored procedure, just as I mentioned last reply, ignore the Stored Procedure in the Schema Compare Options.

Thanks,

Vicky Song


Please remember to mark the replies as answers if they help and unmark them if they provide no help

 

Answer 7

Hi Vicky,

I think this issue also afect other object types. I have seen it in indexes and views as well. And looking at the repro, I realize that is the way I bring chnages to the DB project from the live DB. In my scenario I sometimes have to hand pick few SPs, Views, etc to be part of the DB project. You you suggest is an all or nothing per obejct type. Do you have any other suggestion?

 

Answer 8

Please see my comment above on the workaround ... if you use 'Export to Editor' instead of 'Write Updates', the items you skip will not have the commenting problems.
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter