COALESCE(), Stored Procedure, SELECT INTO, CASE
COALESCE takes any number of arguments and returns the first value that is not null.
COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL
COALESCE can be useful when you want to replace a NULL value with some other value. In this example you show the name of the party for each MSP that has a party. For the MSP with no party (such as Canavan, Dennis) you show the string None.
SELECT name, party
,COALESCE(party,'None') AS aff
FROM msp WHERE name LIKE 'C%'
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
CREATE PROCEDURE procedure_name AS sql_statement GO;EXEC procedure_name;
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Execute the stored procedure above as follows
Stored Procedure With Parameters
list each parameter and the data type separated by a comma as shown below.
The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the “Customers” table:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;EXEC SelectAllCustomers @City = ‘London’, @PostalCode = ‘WA1 1DP’;
SELECT INTO statement copies data from one table into a new table.
INTO newtable [IN externaldb]
Copy only some columns into a new table
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
SELECT * INTO CustomersBackup2017
FROM Customers;SELECT CustomerName, ContactName INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;SELECT Customers.CustomerName, Orders.OrderID
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the
If there is no
ELSE part and no conditions are true, it returns NULL.
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
SELECT OrderID, Quantity,
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;SELECT CustomerName, City, Country
WHEN City IS NULL THEN Country
Thats all Folks…
i recommend you guys check out this amazing tutorials if you are interested and want to learn SQL, https://sqlzoo.net/wiki/SQL_Tutorial
and as always if u have read so far then ..