Drop if exists table or other objects in sql server

More SQL Server Drop If Exists Statements

At the beginning of this article, I stated that 5 of the 20 statements impacted by this new syntax are not used in common practice by most developers.

Listed below are the statements that I did not talk about.

  1. DROP DEFAULT — This statement is deprecated and will be removed in the future. Use a DEFAULT constraint instead.

  2. DROP RULE — This statement is deprecated and will be removed in the future. Use a CHECK constraint instead.

  3. DROP ASSEMBLY — Common language runtime assembly is not support in Azure. Use for quicker algorithm execution with on-premises only version.

  4. DROP AGGREGATE — Common language runtime aggregates are not support in Azure. Use for quicker algorithm execution with on-premises only version.

  5. DROP SECURITY POLICY — This statement is used with row level security. It is a brand new feature released in SQL Server 2016.

Checking if a temporary table exists

MySQL does not provide a function or statement to directly check if a temporary table exists. However, we can create a stored procedure that checks if a temporary table exists or not as follows:


In this procedure, we try to select data from a temporary table. If the temporary table exists, the variable is set to 1, otherwise, it sets to 0.

This statement calls the to check if the temporary table  exists:

Here is the output:

In this tutorial, you have learned about the MySQL temporary tables and how to manage temporary tables such as creating and removing a new temporary table.

  • Was this tutorial helpful?

SQL Server Drop View If Exists

A view is defined as a virtual table whose contents are defined by a query. The following list contains various ways to put a view to good use.

  1. Abstract underlying tables so that users can only see certain columns.
  2. Security can be placed on view to restrict users.
  3. Indexing a view makes it materialized (physical) for speed.
  4. Provide backward compatibility for tables that have changed.
  5. Pre-compiled code that skips the query parsing phase.

Views can be used to provide business information to users that might not be savvy in joining, filtering, grouping and aggregating data using T-SQL. If a SQL developer works with the user to package this logic in a view, a simple SELECT statement can be used to pull the data into MS Excel for analysis.

In our hypothetical business case, the business user wants a list of cars that are ready to sell on EBAY.

The T-SQL code below creates a view named ..

/*  
 Create view 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'.') AND type = N'V')
DROP VIEW .
GO

-- New block of code
DROP VIEW IF EXISTS .
GO

-- Cars ready to sell
CREATE VIEW .
AS
    SELECT  * 
    FROM .
    WHERE MESSAGE_TEXT = 'Sell the toy car.';
GO

-- Use the view
SELECT * FROM  .
GO

One might ask what happens when you try to execute

DROP VIEW statement without an existing view? The following error message is generated.

Or, one might ask what happens when you try to execute

CREATE VIEW statement with an existing View? The following error message is generated.

The output below is from selecting all data from our view.

Building Upon Our Example

To solve the business problem, we need some additional tables.

The . reference table describes a production step as completed, requires rework or should be scrapped.

/*
    Create status code table 
*/

-- Remove table if it exists
DROP TABLE IF EXISTS .
GO

-- Create a STATUS CODE table
CREATE TABLE TOY.STATUS_CODE
(
ID INT IDENTITY(1, 1) CONSTRAINT PK_TOY_STATUS_CODE_ID PRIMARY KEY CLUSTERED,
NAME VARCHAR(20) NULL CONSTRAINT IDX_TOY_STATUS_CODE_NAME UNIQUE 
)
GO

-- Load the table with data
INSERT INTO TOY.STATUS_CODE (NAME) VALUES
('Completed'),
('Rework Needed'),
('Scrapped');
GO

The . contains one row for every model car. This row describes the current step and production state for a given model car. Foreign keys are used to enforce referential integrity between this main table and the two reference tables.

-- 
-- Create current step table 
--

-- Remove table if it exists
DROP TABLE IF EXISTS .
GO


-- Create builds table (latest status)
CREATE TABLE .
(
      (64) NOT NULL,
      NOT NULL,
      NOT NULL,
      NULL,
      (max) NULL,
      NULL,
    CONSTRAINT  PRIMARY KEY CLUSTERED 
    ( 
         ASC,  
         ASC
    )
)
GO


-- 
-- Foreign key constraint 1
--

-- New block of code
ALTER TABLE . DROP CONSTRAINT IF EXISTS FK_TOY_BRANDS_ID;
GO

-- Create constraint
ALTER TABLE .   
ADD CONSTRAINT FK_TOY_BRANDS_ID FOREIGN KEY () REFERENCES . (ID);
GO


-- 
-- Foreign key constraint 2
--

-- New block of code
ALTER TABLE . DROP CONSTRAINT IF EXISTS FK_TOY_STATUS_ID;
GO

-- Create constraint
ALTER TABLE .   
ADD CONSTRAINT FK_TOY_STATUS_ID FOREIGN KEY () REFERENCES . (ID);
GO

The . table contains a row for every insert or update that was performed on the main table. Basically, every step of the production processing for a given model car is recorded in this table. No foreign keys are necessary since the data is coming from the . table.

/* 
    Create history step table 
*/

-- Remove table if it exists
DROP TABLE IF EXISTS .
GO


-- Create MAKE HISTORY table (All Records)
CREATE TABLE .
(
     INT IDENTITY (1, 1),
      (64) NOT NULL,
      NOT NULL,
      NOT NULL,
      NULL,
      (max) NULL,
      NULL,
      NULL,
    CONSTRAINT  PRIMARY KEY CLUSTERED 
    ( 
         ASC,  
         ASC
    )
)
GO

MySQL DROP TABLE statement syntax

To remove existing tables, you use the MySQL statement.


Here is the basic syntax of the statement:

The statement removes a table and its data permanently from the database. In MySQL, you can also remove multiple tables using a single statement, each table is separated by a comma (,).

The option allows you to remove temporary tables only. It ensures that you do not accidentally remove non-temporary tables.

The option conditionally drop a table only if it exists. If you drop a non-existing table with the option, MySQL generates a NOTE, which can be retrieved using the statement.

Note that the statement only drops tables. It doesn’t remove specific user privileges associated with the tables. Therefore, if you create a table with the same name as the dropped one, MySQL will apply the existing privileges to the new table, which may pose a security risk.

The and  options are reserved for the future versions of MySQL.

To execute the statement, you must have privileges for the table that you want to remove.

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Article Syllabus

I will be using the model car business as a case study for showing coding examples for 15 out of the 20 data definition language statements impacted by the new syntax. The last 5 statements are not used in common practice by most developers. Each statement will be showcased with restart able T-SQL code that uses the algorithm below.

  1. Test existence of database object.
  2. Drop database object if it exists.
  3. Create new database object.

The new DROP IF EXISTS syntax replaces the old block of code that used system catalog views to determine the existence of an object. Basically, the new syntax combines steps one and two into a smaller set of code to produce the same results.

For each statement, I give an example of both the old and new way to accomplish the same task. If the CREATE or DROP statements are executed on existing or missing objects respectively, errors will be generated. I show the output of such negative test cases. Last but not least, hyperlinks to each statement are given for the reader to look up detailed information if they desire.

SQL Server Drop Trigger If Exists

Microsoft SQL Server currently supports three types of triggers:

DML,

DDL and

LOGON.

Because we are interested in capturing any changes on the main table, we want to use a DML trigger. DML triggers are a special type of stored procedure that automatically fires when a data manipulation language (DML) event takes place that affects the table or view defined with the trigger. Events that can cause the trigger to fire include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules, data integrity, and audit trails.

Again, we have another choice to make since DML triggers can be defined to fire INSTEAD OF or AFTER a given action. We want to define an AFTER trigger since we do not want to prevent changes to the .CURRENT_STEP] table but we want to capture all actions in the . table.

The T-SQL code below creates the required trigger named ..

/*  
 Trigger to audit changes.
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = 
            OBJECT_ID(N'.'))
DROP TRIGGER .
GO

-- New block of code
DROP TRIGGER IF EXISTS .
GO

-- Move modified record to history table
CREATE TRIGGER . ON .
FOR INSERT, UPDATE
AS
    INSERT INTO .
    (
        ,
        ,
        ,
        ,
        ,
        ,
        
    )
    SELECT
        ,
        ,
        ,
        ,
        ,
        ,
        DATEDIFF(SECOND,,)
    FROM inserted
GO

One might ask what happens when you try to execute

DROP TRIGGER statement on a table without an existing trigger? The following error message is generated.

Or, one might ask what happens when you try to execute

CREATE TRIGGER statement on a table with an existing trigger? The following error message is generated.


SQL Server Drop Function If Exists

User-defined functions are routines that accept parameters, perform an action and return the result of that action as a value. The return value can either be a single scalar value or a result set (table). Wayne Sheffield who is a SQL Server MCM and MVP has shown that inline table value functions (ITVF) are the quickest way to solve a problem. Please take a look at his

article for full details.

We have been asked by the business line to create a function that returns the start time, end time and total duration for building a particular model. Our solution should be a ITVF so that we can call it for a single model or use it in a CROSS APPLY statement for all models.

The T-SQL code below creates the requested function named ..

/*  
 Create function 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'.') AND type = N'V')
DROP FUNCTION .
GO

-- New block of code
DROP FUNCTION IF EXISTS .
GO

-- Total Build Time - Inline TVF
CREATE FUNCTION . 
    ( @MODEL_ID  (64) )
RETURNS TABLE
AS
RETURN 
(
 SELECT 
  , 
  MIN(START_DATE) AS BUILD_START, 
  MAX(FINISH_DATE) AS BUILD_END, 
  DATEDIFF(N, MIN(START_DATE), MAX(FINISH_DATE)) AS TOTAL_MINS 
 FROM 
  . 
 WHERE 
   = @MODEL_ID
 GROUP BY 
  
);
GO

-- Sample call to TVF
SELECT * FROM . ('MUSTANG-1966-0001');
GO

One might ask what happens when you try to execute

DROP FUNCTION statement without an existing function? The following error message is generated.

Or, one might ask what happens when you try to execute

CREATE FUNCTION statement with an existing function? The following error message is generated.

The output below is from selecting data from our inline table value function for the first 1966 Ford Mustang model.

SQL Server Drop Sequence If Exists

The sequence object was added to the database engine in SQL Server 2012. I think this was mainly added to support customers who were moving from Oracle to SQL Server. Before this object was introduced, the

IDENTITY property of a numeric column could be defined to create a sequence of numbers. This property is bound to the column that it is defined on.

In contrast, the sequence object is created at the schema level. Thus, two tables theoretically could use the same sequence object for auto incrementing numeric columns. I do not know a business case that would need this functionality. However, it is definitely possible to create this scenario.

The steps below outline how to create and attach a sequence to a column. The last three steps test the new default constraint.

  1. Drop sequence object if it exists.
  2. Create new sequence object.
  3. Drop default constraint if it exists.
  4. Create new default constraint.
  5. Truncate table.
  6. Restart sequence value.
  7. Insert data into table.

It is important to note that the

NEXT VALUE FOR function does all the heavy lifting in the default constraint. The T-SQL code below creates a sequence named . that is used in the default constraint named DF_TOY_BRANDS_ID .

/*
    Create a sequence object
*/

-- Old block of code
IF EXISTS (SELECT name FROM sys.sequences WHERE name = N'SEQ_BRANDS_ID')
    DROP SEQUENCE .
GO

-- New block of code
DROP SEQUENCE IF EXISTS .
GO

-- Add new sequence.
CREATE SEQUENCE .
    AS INT
    START WITH 100
    INCREMENT BY 1
    MINVALUE 1
    NO MAXVALUE 
    NO CYCLE
    NO CACHE;
GO


/*
    Add default constraint
*/

-- New block of code
ALTER TABLE . DROP CONSTRAINT IF EXISTS DF_TOY_BRANDS_ID;
GO

-- Create constraint
ALTER TABLE .    
ADD CONSTRAINT DF_TOY_BRANDS_ID DEFAULT (NEXT VALUE FOR .) FOR ID;
GO


/*
    Reload data
*/

-- Alter the sequence.
ALTER SEQUENCE . RESTART WITH 1;
GO

-- Remove data
TRUNCATE TABLE TOY.BRANDS;
GO

-- Load the table with data
INSERT INTO TOY.BRANDS (NAME) VALUES
('Ford'),
('Chevy'),
('Dodge'),
('Plymouth'),
('Oldsmobile'),
('Lincoln'),
('Mercury');
GO

One might ask what happens when you try to execute

DROP SEQUENCE statement on a schema without an existing sequence? The following error message is generated.

Or, one might ask what happens when you try to execute

CREATE SEQUENCE statement on a schema with an existing sequence? The following error message is generated.


Adding Test Data to Our SQL Server Example

Before we talk about user defined views and functions, we need to add test data to our database. I am not expert at building model cars. With that said, wikihow had an article on the required steps to assemble any type of toy model. We will use these steps as guidelines for creating our test data.

The T-SQL script below executes the following actions.

  1. Truncate the two tables.
  2. Call the upsert procedure 14 times.
  3. Show data from the two tables.
/*  
 Clear tables
*/

-- Clear the tables
TRUNCATE TABLE .
GO

TRUNCATE TABLE .
GO


/*  
 Fictitious build of toy car
*/

-- Step 1
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 00:00', 
'2011-04-01 00:09', 'Read instructions.', 1;
GO

-- Step 2
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 00:10', 
'2011-04-01 00:29', 'Wash all parts.', 1;
GO

-- Step 3
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 00:30', 
'2011-04-01 00:39', 'Remove large pieces off spruce tree.', 1;
GO

-- Step 4
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 00:40', 
'2011-04-01 00:59', 'Paint small pieces on spruce tree.', 1;
GO

-- Step 5
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 01:30', 
'2011-04-01 01:39', 'Scrap paint off glue edges.', 1;
GO

-- Step 6
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 01:40', 
'2011-04-01 01:49', 'Test fit parts for issues', 1;
GO

-- Step 7
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 01:50', 
'2011-04-01 01:54', 'Use right amount of glue.', 1;
GO

-- Step 8
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 01:55', 
'2011-04-01 02:09', 'Glue parts after test fit.', 1;
GO

-- Step 9
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 02:20', 
'2011-04-01 02:29', 'Fill seams with body putty.', 1;
GO

-- Step 10
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 02:30', 
'2011-04-01 02:39', 'Use newspaper while painting.', 1;
GO

-- Step 11
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 02:40',
'2011-04-01 02:49', 'Use white craft glue for clear pieces.', 1;
GO

-- Step 12
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 02:50', 
'2011-04-01 02:59', 'Inspect painting/gluing for touch ups.', 2;
GO

-- Step 13
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 03:00', 
'2011-04-01 03:09', 'Inspect painting/gluing for touch ups.', 1;
GO

-- Step 14
EXEC . 'MUSTANG-1966-0001', 1, '2011-04-01 03:10', 
'2011-04-01 03:14', 'Sell the toy car.', 1;
GO


/*  
 Show the data
*/

SELECT * FROM .
GO

SELECT * FROM .
GO

The output below shows the test data that should be stored in the . and . tables.

Python NumPy

NumPy IntroNumPy Getting StartedNumPy Creating ArraysNumPy Array IndexingNumPy Array SlicingNumPy Data TypesNumPy Copy vs ViewNumPy Array ShapeNumPy Array ReshapeNumPy Array IteratingNumPy Array JoinNumPy Array SplitNumPy Array SearchNumPy Array SortNumPy Array FilterNumPy Random Random Intro Data Distribution Random Permutation Seaborn Module Normal Distribution Binomial Distribution Poisson Distribution Uniform Distribution Logistic Distribution Multinomial Distribution Exponential Distribution Chi Square Distribution Rayleigh Distribution Pareto Distribution Zipf Distribution

NumPy ufunc ufunc Intro ufunc Create Function ufunc Simple Arithmetic ufunc Rounding Decimals ufunc Logs ufunc Summations ufunc Products ufunc Differences ufunc Finding LCM ufunc Finding GCD ufunc Trigonometric ufunc Hyperbolic ufunc Set Operations

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL Server Drop Procedure If Exists

A stored procedure in SQL Server is a group of one or more compiled T-SQL statements. Procedures can accept input parameters, return multiple output parameters, contain programming statements that perform database operations and/or return a status value to a calling program to indicate success or failure.

To solve our business problem, we need to define a stored procedure that will insert data into the current step table if a record for a given toy car does not exists or update the existing record. This algorithm is commonly called an UPSERT procedure.

The T-SQL code below creates a stored procedure named ..

/*  
 Create upsert procedure 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'.') AND type in (N'P', N'PC'))
DROP PROCEDURE .
GO

-- New block of code
DROP PROCEDURE IF EXISTS .
GO


-- Create the procedure
CREATE PROCEDURE . (
    @MODEL_ID  (64),
    @BRAND_ID ,
    @START_DATE ,
    @FINISH_DATE ,
    @MESSAGE_TEXT  (max),
    @STATUS_ID )
AS
BEGIN

    -- Worry about concurrency
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

    BEGIN TRAN
    
        -- Try the update first
        UPDATE .
        SET 
             = @BRAND_ID,
             = @START_DATE,
             = @FINISH_DATE,
             = @MESSAGE_TEXT,
             = @STATUS_ID
        WHERE  = @MODEL_ID
          
        -- Did not update vehicle
        IF @@ROWCOUNT = 0        
        BEGIN
        
            -- Add new record
            INSERT INTO . 
            SELECT
                @MODEL_ID,
                @BRAND_ID,
                @START_DATE,
                @FINISH_DATE,
                @MESSAGE_TEXT,
                @STATUS_ID
                
            -- Detected error
            IF @@ERROR <> 0
                ROLLBACK
               
        END
    
    -- Save the transaction
    COMMIT
    
END
GO

One might ask what happens when you try to execute

DROP PROCEDURE statement without an existing procedure? The following error message is generated.

Or, one might ask what happens when you try to execute

CREATE PROCEDURE statement with an existing procedure? The following error message is generated.

SQL Server Drop Type If Exists

The TYPE object is used to give a name to commonly used column definitions. In a nutshell, it is short hand for the actual column definition. For instance, the

sysnames data type is defined as a NVARCHAR (128) and is used in many of the system catalog views where object names are defined.

The T-SQL code below creates a type named . to be used where model id is defined.

/*  
 Create type
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.types WHERE NAME = 'MODEL_ID_ADT' and 
    SCHEMA_ID = (SELECT schema_id FROM SYS.SCHEMAS WHERE NAME = 'TOY'))
DROP TYPE  .
GO

-- New block of code
DROP TYPE IF EXISTS .
GO

-- Create the abstract data type (ADT)
CREATE TYPE .  
FROM VARCHAR (64) NOT NULL;  

One might ask what happens when you try to execute

DROP TYPE statement without an existing type? The following error message is generated.

Or, one might ask what happens when you try to execute

CREATE TYPE statement with an existing type? The following error message is generated.


С этим читают