how to have dynamically create & alter in the sql script?
Instead of having if exits - drop we are looking to have if exits - alter.
How to handle such scenario.
4
-
2I don't know what version of SQL Server you're using, but 2016 SP1 introduced a CREATE OR ALTER statement support.microsoft.com/en-us/help/3190548/…userfl89– userfl892019-03-27 19:57:07 +00:00Commented Mar 27, 2019 at 19:57
-
Thank you @userfl89 , i just noticed that syntax. I am going with Create or Altergoofyui– goofyui2019-03-27 19:57:39 +00:00Commented Mar 27, 2019 at 19:57
-
1Just make sure that the object this is being performed on is permitted as specified in the link I provided. For example, views and stored procedures can be used however tables/indexes cannot.userfl89– userfl892019-03-27 20:01:34 +00:00Commented Mar 27, 2019 at 20:01
-
Thanks for that..I will check those details. This will be used for procedures, views and functions..goofyui– goofyui2019-03-27 20:02:55 +00:00Commented Mar 27, 2019 at 20:02
Add a comment
|
2 Answers
To clarify my comments above, SQL Server 2016 SP1 released a CREATE OR ALTER statement that will either create an object that doesn't already exists or modify the object if it does. This is only allowed on certain objects such as stored procedures, triggers, functions, and views. Tables, indexes, and other objects that are allocated storage cannot be used in by the CREATE OR ALTER statement. Also note that since they're persisted on disk, indexes views are not permitted to be used by this. A basic example of the syntax is below.
CREATE OR ALTER PROCEDURE SP_TestStoredProcedure
AS
BEGIN
SELECT
Column1,
Column2,
Column3
FROM YourTable
END
5 Comments
goofyui
What happens when a transaction is happening in the database and we are altering the stored procedure ? What will happen to that existing transaction, whether it will throw an error (or) existing transaction will return the records as per the existing script?
userfl89
As with other DDL operations, schema modification (Sch-M) locks will be taken and this will prevent any access to the object modified during the operation. See the following documentation for more details on this learn.microsoft.com/en-us/sql/relational-databases/…
granadaCoder
Upvote here for 2016 SP1 and forward. A better option. It's about time Sql Server caught up to this one!!
granadaCoder
Now if microsoft can get the ~~only~~ feature of Oracle that I like(d). DECLARE v_empkey EMPLOYEE.EMPLOYEEKEY%TYPE stackoverflow.com/questions/3790658/…
userfl89
@granadaCoder good point, that would definitely be useful for SQL Server!
Here is a trick I've used.
-- for testing, not needed for real -- DROP PROCEDURE dbo.uspDoSomething
GO
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME = 'uspDoSomething' )
BEGIN
EXEC ( 'CREATE PROCEDURE dbo.uspDoSomething(@i INT) AS BEGIN RAISERROR (''Stubbed version'' , 16, -1) END' )
END
GO
--test only
EXEC dbo.uspDoSomething 0
GO
ALTER PROCEDURE dbo.uspDoSomething(@PatientKey INT)
AS
BEGIN
SELECT @@VERSION
END
GO
--test only
EXEC dbo.uspDoSomething 0
GO
Remember, an ALTER does not change all the PERMISSIONS you have on the script. A DROP/ADD needs permissions reapplied.
Note, you did not originally mention your sql-server version. This trick works with 2014 and before. Obviously, newer versions with CREATE OR ALTER would be preferred over EXEC with dynamic sql.
6 Comments
goofyui
I agree in terms of permission. However, i am still not clear in the terms of transaction. Whether the transactions will take place or not?
granadaCoder
"ALTER PROCEDURE" is atomic. Therefore this is a much better solution IMHO than drop/(re)create.
userfl89
@granadaCoder Great info for prior versions of SQL Server. +1
goofyui
Hope the existing transactions go through without having any interruptions.
granadaCoder
Ok! You mean existing calls to the procedure will "complete". I ~~think~~ it will actually (again, ALTER is atomic), but that would have to be tested. #fingersCross
|