Apex SQL Refactor is a SQL Server Management Studio and Visual Studio add-in for SQL formatting with a collection of useful code refactors.
In this article, we will describe one particular refactor for safe parameter modification.
Solution to this problem is to avoid updating those fields which have corresponding parameters with NULL values or, we can update existing data corresponding to those fields. Now, the modified script may look like CREATE PROCEDURE [dbo].[Updt Employee] ( @Emp Id INT, @Emp Name VARCHAR(100)=NULL, @Address VARCHAR(200)=NULL, @Emp Photo Path VARCHAR(100)=NULL, @Mobile No VARCHAR(14)=NULL ) AS BEGIN UPDATE dbo.
Modifying parameters of a SQL function or a stored procedure is not a simple task if we want to keep dependencies and maintain database integrity.
I see a Connect item to implement CREATE OR REPLACE as DDL, but this is a good way to get around it. So all my stored proc code in source control has the GRANT statements with it.
I've been trying to get the developers where I work to use this kind of "non destructive" method, especially since they don't include the GRANTs with the stored proc code in source control.
I was about to update a report with new fields in a stored procedure for the first time, and since I read your report first. I hope you write many more to keep informing us less experienced developers. When I went threw the verify database process, at the end it told me it could not find the table. In this class object i have written different methods and properties and i am assigning List to report datasource.
But with each release of SQL Server that list grows shorter.Thanks for that, CR is the most frustrating software I've ever had to use - and I have M$ Office 2007 installed! "Loose" is how I've been with my tongue after using CR. As you had mentioned in your article like veryfiy database, but to me its directly saying 'database is uo to date'. If i enter a value, it is remembering value and each time when i generate report it is using the saved value as input instead of input given by the user."Lose" is what I did with my marbles for going back time after time to continue using it. Is there a way to skip this step to enter a input value while updating a report.Hi have a situation where I have to change the store procedure,but when I do "Set Datasource Location" I get error. Initially my Store Procedure x, which has 3 parameters and now I want to switch to Store Procedure y, which has 4 parameters. Jim Paisley asked: Is there a way to refresh fields for a proc in 11.5 without removing it and having to add all the fields again? I am really excited and just want to share with you guys about my finding: I finally figured out the fix for the mystery loading problem, I want to share my pain/solution with everyone else so hopefully someone can benefit from it: My report's data source comes from two stored procedures, the two stored procedures have a common field, by default, CR links these two sp using that common field and this is where the problem is: if the report has used only one sp's data, then everything is fine; if you add one other sp's data into it, it doesn't generate error for you, but will not load any data.So, the fix is to unlink the two stored procedures and voila!