Sharing is Caring | Learning Everyday
A view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Modify TABLE data through a VIEW
UPDATE view_name
SET column_name='Himen'
WHERE column_name= N'Patel' ;
RETURN variable can only return integers and that too, only one integer. It is not possible to return more than one value.
They are used to return SUCCESS and FAILURE.
OUTPUT variable, we can return more than one value and any datatype. They are used to return name, count, etc.
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.
Stored procedures accept input parameters so that a single procedure can be used over the network by
several clients using different input data. And when the procedure is modified, all clients automatically get the new version.
Stored procedures reduce network traffic and improve performance.
Stored procedures can be used to help ensure the integrity of the database.
CREATE PROCEDURE usp_SelectRecord
AS
BEGIN
SELECT * FROM TABLE
END
GO
Error Handling in SQL Server
CREATE PROCEDURE procedure_name AS
BEGIN TRY
PRINT 'My name is Himen'
SELECT * FROM dbo.Users
PRINT 'This will not print - My name is Himen'
END TRY
BEGIN CATCH
PRINT 'And nor does this print'
END CATCH
go
Creating SQL Server Tables Syntax
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Alter SQL Server Table syntax (ADD Column)
ALTER TABLE table_name ADD column_name datatype
Alter SQL Server Table syntax (DROP Column)
ALTER TABLE table_name DROP column_name datatype
Tech Corner 