More Stored Procedures (Add and Update)

Given below is a stored procedure for adding data into a table.
ex3-

create procedure MyStoredProcedure3
(
@para1 varchar(100),
@para2 int,
@para3 varchar(500)
)
insert into [Table1]
values (@para1,@para2,@para3)

note here that the parameters you input (@para 1,2 and 3) will set stored in the 1st, 2nd and 3rd columns respectively. And here you need to specify data for all the columns in the table. But, if you want to make it specific you can try the below example.

create procedure MyStoredProcedure3
(
@para1 varchar(100),
@para2 int,
@para3 varchar(500)
)
insert into [Table1]([column2],[column3],[column4]
values (@para1,@para2,@para3)

Here para1, 2 and 3 will get stored in the column 1, 2 and 3 of the table. The other columns in the table should allow nullable values, except for the primary key column which in this case can be auto-incremented. Also note that when you input data to an auto-incremented primary key column it will throw an exception.

Given below is a stored procedure for updating a row in a table.
ex4-

create procedure MyStoredProcedure4
(
@para1 int,
@para2 varchar(100),
@para3 varchar(500)
)
update [Table1]
set [column2]=@para2,[column3]=@para3
where [column1]=@para1

Good Day! See you next time with some more examples..

Web Development and Design - Begining with SQL

Creating  Stored Procedures- Basics

 For this, you should have the basic knowledge on creating queries in SQL(visit http://www.w3schools.com/sql/default.asp).

Wat is really a stored procedure?

It is more like making the sql statements programmable! These stored procedures are stored in the database. It can take parameters and return what you want. Then you can easily call this procedure from a remote application, and get your result. It begins with the keyword create procedure

ex1-

Here is an example for the select query.

(If you want the whole records in the table)

create procedure MyStoredProcedure1

as

select * from [TableName] 

ex2-

Here is an example for the select query with input parameters. (If  you want a particular row / set of row- using the where condition)

Here, the input parameter will be given by the user which is dynamic.But, when using a SQL statement we write the where clause like,

select * from [TableName] where [columnName] = 23. The value 23 should get changed according to what user inputs. For that we can write a procedure like ,

 

create procedure MyStoredProcedure2

(

/*input parameters with their type*/

@para1 varchar(50)

)

as

select * from [TableName] where [columnName] = @para1

Here the type of the parameter given should be the type of its corresponding [columnName].

In the next lesson we will look into more examples on stored procedures.

Good Day.