Sql Command and Stored Procedure
What is SQL?
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.
SQL Commands
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature
Insert: Creates a record
Update: Modifies the record
Delete: Delete the records
Create: Create a new tables, view of tables, or other object in database.
SQL CREATE TABLE Statement
- The CREATE TABLE statement is used to create a new table in a database.
Syntax
Create Table table-name(
column1 datatype,
column1 datatype,
column1 datatype,
….
)
- The column parameters specify the names of the columns of the table.
- The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
SQL CREATE TABLE Example
- The following example creates a table called “Persons” that contains five columns: PersonID, LastName, FirstName, Address, and City
CREATE TABLE Persons(
PersonID Int,
Last name varchar(255),
Firstname varchar(255),
Address varchar(255),
city varchar(255)
);
The PersonID column is of type int and will hold an integer. The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.
SQL INSERT INTO Statement
INSERT INTO statement is used to insert a new records in a table.
It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted:
Syntax
INSERT INTO table-name(column1, column2, column3,… ) values(value1, value1, value1, … );
If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:
Syntax
INSERT INTO table_name VALUES(value1,value2,value3, … )
SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
Syntax
UPDATE table_name
SET column1=value1, column2=value2, …
where condition;
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.
UPDATE customers
SET contactname=’Baharia Town’, city=’Islamabad’
where customerID=1;
SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
syntax
DELETE FROM table-name WHERE condition;
The following Alfreds Futterkiste” from the “Customers” table
DELETE FROM customers WHERE customername-‘ALI’;
What is a Stored Procedure?
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.
Syntax
Create procedure[dbo].[spw-userregistration]
(
@fname varchar(50),
@lname varchar(50),
@email varchar(50),
@password varchar(50),
@city varchar(50),
@country varchar(50),
@phone numeric
)
as
begin
insert into tblRegistration(Fname, Lname, Email, Password, City, Country,Phone)
values(@fname, @lname,@email, @password, @city, @country, @phone)
end
1. How to create database
First start SQL server then right click on Database and select new database after that in right column put an name you want for your database and press ok
2. How to create Table in database
After creating the database expend this folder in left column than right click on table folder and hit new table
After filling all the table attribute, Datatype and Allow Nulls value right click on ID column start and make it primary key after this in right below click on identity specification make it yes in just clicking in right column
hit Ctr+S popup appear give table name and press ok
3. How to create stored procedure
After that click on NEW Query the following empty table will open, you have to write your stored procedure code here
Sign up pages procedure code, after writing the following code press Execute button to just create it
4. Check Stored Procedure working
After successful procedure execution expand the programmability> Stored Procedures find your procedure right click on this and than press run and fill the procedure value to make sure procedure is correct or not
After successful execution the following screen show with return value 0
How to check Table values
To check the submitted value in procedure click on table name and hit top 50 row that show submitted values
