Sunday, October 31, 2010

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..