Home » Visual Studio

Stored Procedure Parameters Being Commented Out


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



/*@foundation_userbit = null*/,

/*@foundation_adminbit = null*/,

/*@foundation_eabit = null*/,

/*@foundation_grant_managerbit = null*/,

/*@admin bit = null*/,

/*@superadminbit = null*/






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?




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.


Vicky Song

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


Answer 2


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.


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]


CREATEPROCEDURE [dbo].[Procedure1]

      @param1 int= 0,

      @param2 int


      SELECT @param1, @param2


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]






ALTERPROCEDURE [dbo].[Procedure1]

      @param1 int= 0


      SELECT @param1


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.


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




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.


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.


So, I'm a bit confused by the "Set Select procedure parameters" part of the Bind Commands to Existing Stored Procedures  portion of the TableAdapter Configuration Wizard.

I have a VS 2010 C# project with a dataset. I have added a pre-existing StoredProcedure from one of my SQL Server databases to the dataset. The stored procedure takes 3 parameters.

I can't figure out what the purpose of the "Set Select procedure parameters" list box (to the right on the Wizard) is for or how to use it. The parameters that I pass to the StoredProcedure become values for filtering and all of this is assigned in the StoredProcedure's code (located on the SQL Server). Why must I map a parameter to a datacolumn?

And, I can't seem to figure out how I would map more than one parameter since you can only select one Data Column in the list box and there's no way to identify which parameter maps to which column.

Am I missing the purpose of this?



Using Visual Studio Team System 2008 Database Edition GDR Version 9.1.40413.00

Created a DB project, imported a DB objects from a SQL Server 2000 DB. Build the project and got the *.sql deployment script. Reviewing the SQL script, I noticed that all the comments of UDF and SP are all dropped.

In the original DB, the comment section was created above the CREATE statement. Example:

Here is the purpose of this SP
2009-12-16 - Author name, created
@param1 intAS
@param1 AS DemoValue1
Navigating in the DB Project, if I double click on the sql file corresponding to any UDF or SP, the editor displays correctly the comment block followed by the CREATE statement (like in the original DB).

Question: what is the reason for VSTS DB 2008 for not including the original SP/UDF comment block in the deployment script? May be the comment block should be placed below the CREATE statement?

Thanks in advance for any help.

EDIT: same behavior for the change script resulting from the Schema compare. If the source stored proc is changed, the ALTER PROCEDURE of the generated "Schema Update Script" doesn't include the original comment block.



I am having a stored procedure(SP) with one parameter say SP_Test.

Now in another SP say SP_Test1 i need to execute the above SP and get the row count or result count for some manipulation.

When i use

create proc SP_Test1


    declare @count int

    exec SP_Test

    set @count = (select @@rowcount)

    select @count


i am getting two result set .

1. the result set of SP_Test and

2. The count result

How to get the second result set alone. Please guide me.


Umar Ali


I'm wondering how a dataset returned by a stored procedure can be passed to another stored procedure.



mark it as answer if it answered your question :)

I want to call a stored procedure within my main stored procedure to return a value.

Here is the code snippet:   EXECUTE @ClaimNumber =  [dbo].[uspGetNextNumber] 'BRP_INT' , 'ClaimHeader', 'ClaimNumber', 'dlivelsb'

When I run uspGetNextNumber by itself thru debugger, the proc returns the value. When I run the proc inside my stored procedure, it returns zero.

Any thoughts on why this is not working?



I have store procedure need to call another store procedure to return value.

Create PROCEDURE Testone


        exec TestTwo   --return value


How to get the return value when value from second store procedure.

As always, your help is highly appreciated.


Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)


Finished running [dbo].[DeleteSetByTime].

This is my function:






RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:






SET @TTL = dbo.TTLValue()

DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)


CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.


Is it possible to use a stored procedure to create a bunch of other stored procedures?  When the user creates a new database, I need to create a bunch of stored procedures and I would like to just run a stored procedure on the new database that creates all the needed stored procedures.  I tried just putting the CREATE PROCEDURE syntax inside a stored procedure and got some errors.  I imagine this can be done using dynamic sql and the EXECUTE function but I was hoping there was a better way.




Hello. I am new to the forum system... go easy on me :)

I am working in SQL Server Management Studio 2008. I have a stored procedure, say st_alpha and a second stored procedure, say st_beta . If I execute st_alpha , and within it, st_beta is executed, then st_beta writes to a log, is there a way to write in the log which stored procedure executed st_beta ? St_alpha could be any stored procedure in our system, st_beta on the other hand, is the same stored procedure that could be executed from within any st_alpha. I would just like to write a record to the log from st_beta as to which stored procedure executed st_beta without having to go through all of the st_alpha procedures and add a description variable that is passed to st_beta.


Any ideas?


hi experts,

  In control flow

Execute SQL Task --> executes  the stored procedure and on completion pointer return to Script Task --> which will check the returnvalue of the stored procedure and if return value is not zero then display an error message using message box.

What i want is how do i write the message to a flat file instead of displaying as message box. plz help me with sample code.


My code for display message box in script task

  int intRetun = Convert.ToInt32(Dts.Variables["intReturnVal"].Value);
            string strErrorMessage = Dts.Variables["strSqlErrorMsg"].Value.ToString();


            if (intRetun != 0)
                MessageBox.Show(" Stored procedure return an error. \n" + "Error Message : " + strErrorMessage + "\n" + "Error Code : " + intRetun.ToString(), "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error);








I am call stored procedure from PHP and using the following codes:

$AssessmentID = 0;

$params = array(

array($ShopPostCode, $GrossProfit, $LevyDue,$PartYear,$FromDate,$ToDate,

$ClosedInYear, $OpenedInYear,$BookmakerRef, $Scheme, 

       $AssessedActivityID, SQLSRV_PARAM_IN),

array($AssessmentID, SQLSRV_PARAM_OUT)


 /* Execute the query. */

 $stmt = sqlsrv_query( $dbconn, $sql, $params);

echo "Output: " . $AssessmentID . "<br />";


The stored procedure take the parameters and insert the values in table and return newly create AssessmentID.

This is giving me the following error message:

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -15 [code] => -15 [2] => An invalid direction for parameter 1 was specified. SQLSRV_PARAM_IN, SQLSRV_PARAM_OUT, and SQLSRV_PARAM_INOUT are valid values. [message] => An invalid direction for parameter 1 was specified. SQLSRV_PARAM_IN, SQLSRV_PARAM_OUT, and SQLSRV_PARAM_INOUT are valid values. ) )


Any ideal why..

thanks for your time.


CREATE PROCEDURE ps_StudentList_Import
@PathFileName varchar(100),
@tablename varchar(100)


DECLARE @SQL varchar(2000)

  SET @SQL = "BULK INSERT '"+@tablename+"' FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""',ROWTERMINATOR = '""\n""') "
 EXEC ps_StudentList_Import 'c:\data\DimActivity.txt' ,'dbo.DimActivity';
 here the 'dbo.DimActivity' is a table as parameter cannot we pass the table as a parameter?
 what is wrong with my above code..please anyone help me out...


How do i set dates for default of pram value in the strored proc...


i'e i want the last month 6/2010  - for end date


then the first month of the year in 2010

I had a nasty problem for the last two days. I have some stored procedures on my sql server 2008 with (interger) output parameters.
I was able to query those procedures without any error, but I wasn´t getting back any result values in my output parameters.

I now have a solution I want to share with this forum.

This problem occurs if inside the stored procedure are any nested queries results. You can see if there are additional results inside the procedure by testing the stored procedure in SQL Management studio and getting back several "XXX rows affected" lines in the messages window.

If you follow the PHP driver Programming Guide for Directional Parameters you will fail in the described situation.

The source of the problem is how the sql server protocol is handling the parameter transfer:

The problem is, the parameters are transfered as the last packet over the network. The driver is traversing the results one by one, with the ouput parameters read AFTER all other results have been read. Each "XXX rows affected" will become a result to be (manually, triggerd) read from the server - if you read this "garbage" resuls with sqlsrv_fetch_array or similar you will see something like "the result set contains no data" as an returned server error.

You just have to skip this "garbage" to get your output parameters as results. You can skip those with the following commands:

        $tsql_callSP = "{call dbo.aStoredProcWithOutputParam(?)}";

        $params = array(
              array (

        $stmt = sqlsrv_query( $con, $tsql_callSP, $params);

        --- Now you have to skip all garbage
        $loop = true;
        while ($loop)
          $loop = sqlsrv_next_result( $stmt );

         This will iterate through all results until (internally in the driver) SQLMoreResults returns SQL_NO_DATA -> now Output Parameters are ready to be read because the last result data packet has been transfered from the server to the client, which contains the output parameters.

       echo $outputvar

       It is NECESSARY that you loop through with a while clause because there can be several "garbage" results.

       If you don´t do the described iteration, you will not have a result in your output parameter php variables. I am not sure wether this problem only occurs in the case the output parameter is an integer (as described in the Microsoft article "Process Return Codes and Output Parameter"). In my case the output parameters were of an integer type.

Please be careful if you have results from a select command as a stored proc result. Make sure that you don´t skip them if you need them. I doesn´t tested this situation so I just want to warn you that you may get the output parameters but accidentially skip the other results from the stored proc.


I am confused by how to define a parameter in my Stored Procedure where the SSRS report definition says that it allows multiple values. For instance, if I have a column nvarchar(20) that I want to filter on so include it as a parameter. How would I define that in my SP to an unknown number of values?

I'm baffled by how it holds these internally as there are no arrays, I think? Is it just a large string field?


I'm using VS2010 and .NET 4.  I have a DataGrid that has Insert, Update and Delete stored procedures mapped to it's datasource (an EF entitiy).  The grid is bound to an EF entity and all the stored procedures are working great except for insert.  The grid's collection is a list of locations that have a specific quoteID.  If I need to add a location, I need to pass that quoteID into the Insert stored proc. Do I need to manually handle inserts?

And if I need to manually handle those tasks, has anyone figured out an easier way to get to the DataGrid's cell contents?  I think I've looked at close to 5 billion websites on getting at a DataGrid's cell contents, none of which has worked out very well for me. Doesn't help that most solutions are for the WPFToolkit grid, rather than the .NET 4 grid.

Thanks for the help.


I want to load a table with a OLE DB Source by a SQL command which shrinks the data to be loaded to a given time interval. Example:

    SELECT *
      FROM myTable
    WHERE loadDate = startDate

The value of startDate should be retrieved from another table containing this value for sevaral tables to be loaded by ETL processes. The metadata of this 'control table' looks like:

    CREATE TABLE control (
        tablename VARCHAR(100),
        startDate DATE)

I have implemented a stored procedure on SQL SERVER returning this startDate (as datatype DATE)  for a given tablename. This function alone works very well. Now I'm looking for a good way to pass startDate into the OEL DB Source above. My idea was to use a SQLTask which is calling the stored procedure and stores the resultset into a variable of type datetime. But this throws following exception:

"0xC002F309 at Execute SQL Task ... "The type of the value being assigned to variable "User::startDate" differs from the current variable type...."

Setting of the SQLTask:
ResultSet Type:    single row
ResultSet:    0, user::startDate
no Parameters
SQL-Statement:    SELECT dbo.f_get_startDate('Test')

My questions:
1) Why does the DATE result of the function not fit into a DATETIME variable?
2) Is it possible to assign the function result directly to a variable without a SQLTask?
3) Is there a better way to set startDate in the OEL DB source above?

Thank you in advance!



I am very new to VB and Visual Studio 2005. I am trying to get this code below to work but I am getting a Cross threading error in the "Bwg1,backgroundworker" step. What this application does is that it allows users to enter data on a form and with these parameters update tables in a  SQL DB. Please help...



Class MainForm


PrivateSub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click




'MainCloseButton.Enabled = False

MainViewRPTButton.Enabled =



bgw1 =

New System.ComponentModel.BackgroundWorker



bgw1.WorkerSupportsCancellation =



' Starts BGW1_DoWork process




PrivateSub BGW1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgw1.DoWork


' End Sub


'Private Sub BGW1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgw1.RunWorkerCompleted


'Private Sub BGW1_ProgressChanged(ByVal sender As System.Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgw1.ProgressChanged




'Clears data from Noxious tables.


Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("SP_ClearALLData", cnn)


'Makes a Copy Property_Char table and names it Property_Char_CPY located in Noxious DB


Dim command2 As SqlClient.SqlCommand = New SqlClient.SqlCommand("SP_DupProperty_Char_CPY", cnn)


'Edits Property_Char_CPY table.


Dim command3 As SqlClient.SqlCommand = New SqlClient.SqlCommand("SP_Clean_Property_Char_CPY", cnn)


'Inserts data into Noxious.DBO.NW_FIR.


Dim command4 As SqlClient.SqlCommand = New SqlClient.SqlCommand("SP_NW_FIR", cnn)


"@taxyear", TaxYearTextBox.Text)


"@pvc1", pvc1ComboBox.Text)


"@pvc2", pvc2ComboBox.Text)


"@pvc3", pvc3ComboBox.Text)


"@pvc4", pvc4ComboBox.Text)


"@pvc5", pvc5ComboBox.Text)


"@pvc6", pvc6combobox.Text)


"@forestrateparcel", ForestLandRatePerParcelTextbox.Text)


"@forestrateacre", ForestLandRatePerAcreTextbox.Text)
















I googled this issue and I was not able to come up with any solution... not sure what I'm doing wrong. Whatever I do, either I get an error (different error based what I try) or only the first value is displayed. This is for SSRS 2005

I'm using the Stored Procedure below and in my Dataset I only have the SP name and the Command type is SP. In the Dataset Parameters, the Name is @ClientID and the value is =JOIN(Parameters!ClientID.Value, ",")

The Report Parameters has Multi-Value and Allow blank value selected... in the Available Values, From query is checked and I use a simple query that would pull the ClientID and the ClientName from another DataSet:

 KC.ClientID, KC.ClientCode + ' - '
 + KC.ClientName as

I Found the Split Function in WHERE clause from some site (http://www.bidn.com/blogs/mikedavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs)

ALTERPROCEDURE [dbo].[spr_ServerInfoByClients]
@ClientID nvarchar(4) AS
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;

SELECT KC.ClientID, KC.ClientCode, KC.ClientName, KS.ServerName, KS.ServerDesc, KS.ServerIPAddress, KS.ServerType FROM CLIENTS KC INNERJOIN SERVERS KS ON (KC.ClientID = KS.ClientID)
WHERE KS.ClientID IN (SELECT Item FROM Split(@ClientID, ','))




I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it.

the declaration goes as follow


  <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/>
  PublicSharedSub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> OptionalByVal De AsString = "", <SqlFacet(MaxSize:=100)> OptionalByVal Para AsString = "", _<br/>
                 <SqlFacet(MaxSize:=100)> OptionalByVal Copia AsString = "", <SqlFacet(MaxSize:=100)> OptionalByVal Oculta AsString = "", _<br/>
                 <SqlFacet(MaxSize:=100)> OptionalByVal Assunto AsString = "", <SqlFacet(MaxSize:=-1)> OptionalByVal Mensagem AsString = "", _<br/>
                 <SqlFacet(MaxSize:=-1)> OptionalByVal Anexo AsString = "", _<br/>
                 <strong><SqlFacet(MaxSize:=1000)> <Out()> OptionalByVal Retorno AsString = ""</strong>
, _<br/>
                 <SqlFacet(MaxSize:=100)> OptionalByVal Smtp AsString = "", <SqlFacet(MaxSize:=100)> OptionalByVal Usuario AsString = "", _<br/>
                 <SqlFacet(MaxSize:=100)> OptionalByVal Senha AsString = "")


my problem is right here <SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""

in the end of the procedure i simply set Retorno = "success message", but upon deploying this procedure i get the following error

Declarations do not match for parameter 8. .NET Framework reference and T-SQL OUTPUT parameter declarations must match

I tried declaring it as SqlString but i don't know how to set it's default.

originaly this parameter wasnt optional, but in order to maintain the order it has to be.

I am as of this point clueless on what to do to make this work, so any help/idea is much appreciated


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure