2

Whilst working on a SQL Server 2008 database project in Visual Studio 2010 I added a new parameter to an existing stored procedure definition. When I built the project it failed to detect that references to the sproc elsewhere in the project did not have enough parameters. It even let me deploy the project.

Is this the way it's meant to behave or have I forgotten to tick a box somewhere?!

Sam : )

samaspin
  • 2,342
  • 1
  • 26
  • 31

2 Answers2

4

Database projects do not detect problems with procedure/function parameters. Also, you will notice you can delete the offending procedure/function from your project all together and it won't fail.

In my case, I use an external tool for managing programmability, so not failing the build because of missing procs is a plus.

If you want to validate your procedures and functions you can write a scipt that will execute all your stored procedures with using "SET FMTONLY ON". The procedure will be compiled, but no permanent changes will be made to the DB during execution. You can't use this with procedures that use temporary tables (#table syntax).

That's how Microsoft does it in Visual Studio to determine what the output of your stored procedure should be.

Carter Medlin
  • 11,857
  • 5
  • 62
  • 68
  • Wow, great answer! Thanks. Out of interest what is the external tool you are using? – samaspin Aug 18 '11 at 08:28
  • A program a friend of mine wrote for managing stored procs/functions as XML code stubs. If you are interested I could send you a copy. – Carter Medlin Aug 18 '11 at 15:50
  • Yeh thanks, if you could send me a copy that would be great! I dont really understand how you're using it but am always interested in new approaches to managing the stuff! – samaspin Aug 22 '11 at 10:30
  • @BAMBAM. Put an email or some method to contact you in your profile and I'll send it to you. – Carter Medlin Aug 22 '11 at 17:52
0

Unless you re-run the code generation wizard (by deleting the sproc in the VS Server Explorer then dragging it back in) your project doesn't know that the database has changed. You may get runtime errors but not compile errors.

If it doesn't know about any changes it will compile normally. So yes, it's supposed to behave that way.

lettucemode
  • 422
  • 4
  • 14
  • Im developing inside Visual Studio, I modified the sproc inside Visual Studio project and saved the project. If I had made the change in Management Studio then I would agree that I would need to remove and re-add it. – samaspin Aug 11 '11 at 16:33