0

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.

goofyui
  • 3,362
  • 20
  • 72
  • 128
  • 2
    I don't know what version of SQL Server you're using, but 2016 SP1 introduced a CREATE OR ALTER statement https://support.microsoft.com/en-us/help/3190548/update-introduces-create-or-alter-transact-sql-statement-in-sql-server – userfl89 Mar 27 '19 at 19:57
  • Thank you @userfl89 , i just noticed that syntax. I am going with Create or Alter – goofyui Mar 27 '19 at 19:57
  • 1
    Just 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 Mar 27 '19 at 20:01
  • Thanks for that..I will check those details. This will be used for procedures, views and functions.. – goofyui Mar 27 '19 at 20:02

2 Answers2

2

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
userfl89
  • 4,610
  • 1
  • 9
  • 17
  • 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? – goofyui Mar 27 '19 at 20:13
  • 1
    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 https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017 – userfl89 Mar 27 '19 at 20:20
  • 1
    Upvote here for 2016 SP1 and forward. A better option. It's about time Sql Server caught up to this one!! – granadaCoder Mar 27 '19 at 20:36
  • Now if microsoft can get the ~~only~~ feature of Oracle that I like(d). DECLARE v_empkey EMPLOYEE.EMPLOYEEKEY%TYPE https://stackoverflow.com/questions/3790658/what-does-type-mean-in-oracle-sql/3790693#3790693 – granadaCoder Mar 27 '19 at 20:38
  • @granadaCoder good point, that would definitely be useful for SQL Server! – userfl89 Mar 27 '19 at 21:29
1

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.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • 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? – goofyui Mar 27 '19 at 20:25
  • 1
    "ALTER PROCEDURE" is atomic. Therefore this is a much better solution IMHO than drop/(re)create. – granadaCoder Mar 27 '19 at 20:27
  • 1
    @granadaCoder Great info for prior versions of SQL Server. +1 – userfl89 Mar 27 '19 at 20:28
  • Hope the existing transactions go through without having any interruptions. – goofyui Mar 27 '19 at 20:28
  • 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 – granadaCoder Mar 27 '19 at 20:36
  • What exactly meant by your term alter is atomic. ? What are you trying to say by that – goofyui Mar 27 '19 at 20:38