Sample for create store procedure.
-- pattern mssql store procedure
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
-- Converting procedure
ALTER PROCEDURE [dbo].[Convers]-- 1,4,5
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 1,
@sql varchar(49) = ''
AS
BEGIN
print (@p1 + @p2) -- plus int
-- convert int to varchar and append varchar
print convert(varchar,@p1+@p2)+@sql
print cast(@p2 as varchar(2)) -- convert int to varchar
declare @myDate varchar(50)
select @mydate = birthdate from test_1
--select birthdate from test_1
END
--select *,'test'=id +'-'+ convert(varchar(50),birthdate) from test_1
--select *, id +'-'+ convert(varchar(50),birthdate) as test from test_1
--select str(id, 10, 2) from test_1
--select convert(varchar(50), birthdate, 111) from test_1
-- End Converting
-- If
ALTER PROCEDURE [dbo].[If]
AS
BEGIN
if exists (select * from test_2)
begin
print 'Exist'
end
else
begin
print 'Not exists'
end
if 1 <> 1 OR 1 = 1 OR 2 >= 1
print 'True'
else
print 'False'
END
-- End if
-- Insert from select statement
ALTER PROCEDURE [dbo].[Insert_table_to_table]
AS
BEGIN
-- delete test_2
-- delete from test_2
-- INSERT INTO [sample].[dbo].[test_2]
-- ([id_2]
-- ,[birthdate_2]
-- ,[name_2])
-- select id, birthdate, name from test_1
INSERT [sample].[dbo].[test_2]
([id_2]
,[birthdate_2]
,[name_2])
select id, birthdate, name from test_1
END
-- End Insert from select statement
-- Set value
ALTER PROCEDURE [dbo].[Set]
AS
BEGIN
declare @temp varchar(50)
select @temp = id from test_1
-- or select statement must inside braces
set @temp = (select id from test_1)
print @temp
set @temp = @temp + ' hello '
print @temp
END
-- End set value
-- While
ALTER PROCEDURE [dbo].[While]
AS
BEGIN
declare @i int
set @i = 10
while @i > 0
begin
print @i
set @i = @i - 1
end
END
-- End while
-- check exist befor create table ที่มา-- check ว่ามี table นี้อยู่หรือไม่ ถ้ามี ให้ drop ก่อน
if exists(select name from sysobjects where id = object_id('abc'))
begin
print 'try to drop table abc'
drop table abc
end
go
-- สร้าง table abc
create table abc (
code char(2) not null,
description varchar(20) null,
active bit null default 0)
-- สร้าง unique index
create unique index uni_abc on abc(code)
-- ทดสอบ insert ข้อมูล
insert into abc values ('AA','TEST 1',1);
-- check ว่ามี proc นี้หรือไม่ ถ้ามี drop ก่อน
if exists(select name from sysobjects where id = object_id('proc_abcInsUpdDel'))
begin
print 'try to drop proc proc_abcInsUpdDel'
drop proc proc_abcInsUpdDel
end
go
-- สร้าง store proc insert/update/delete
create proc proc_abcInsUpdDel
@option tinyint,
@code char(2),
@description varchar(20)=null,
@active bit=null
as
if @active is null
select @active = 1
if @option = 1
begin
insert into abc(code,description,active) values (@code,@description,@active)
if @@error <> 0
begin
raiserror( 'proc_abcInsUpdDel:ไม่สามารถบันทึกข้อมูลได้',16,-1)
end
else
begin
print 'save complete'
end
end
if @option = 1
begin
insert into abc(code,description,active) values (@code,@description,@active)
if @@error <> 0
begin
raiserror( 'proc_abcInsUpdDel:ไม่สามารถบันทึกข้อมูลได้',16,-1)
end
end
if @option = 2
begin
update abc set description=@description,active=@active
where code = @code
if @@error <> 0
begin
raiserror( 'proc_abcInsUpdDel:ไม่สามารถบันทึกข้อมูลได้',16,-1)
end
end
if @option = 3
begin
delete from abc where code = @code
if @@error <> 0
begin
raiserror( 'proc_abcInsUpdDel:ไม่สามารถบันทึกข้อมูลได้',16,-1)
end
end
-- ทดสอบ insert
proc_abcInsUpdDel 1,'BB','TEST2',1
-- ทดสอบ insert โดยไม่มี active
proc_abcInsUpdDel 1,'CC','TEST3'
-- ทดสอบ update
proc_abcInsUpdDel 2,'CC','TEST4'
-- ทดสอบ delete
proc_abcInsUpdDel 3,'CCC'
-- ตรวจสอบข้อมูล
select * from abc;
Notes:- การเรียก function ที่เราสร้างเองใน procedure ต้องเรียกแบบนี้ dbo.ชื่อฟังก์ชั่นเรา
- dbo = database owner
- คือต้องเติม dbo. หน้าชื่อ function ของเรานั่นเอง
- ก่อนจบอ่ะ อีกนิดคือ
วิธีการรับค่า return จาก procedure กลับมายัง app เรา อ้างอิงบน C#// สร้างตัวแปรแบบ sqlparameter ขึ้นมา 1 ตัวชื่ออาไรก็ได้ ในที่นี้ให้ชื่อ ReturnValue อาจจะไม่ใส่ @ ข้างหน้าก็ได้
SqlParameter returnparam = new SqlParameter("@ReturnValue", SqlDbType.Int);
// กำหนดให้ตัวแปรนี้เป็นการรับค่ากลับจาก procedure
returnparam.Direction = ParameterDirection.ReturnValue;
//และเพิ่มเข้าไปใน sql command ที่เรียก procedure
cmdStored.Parameters.Add(returnparam);
// เอ๊กคิ้ว sql command
cmdStored.ExecuteNonQuery();
// สร้างตัวแปรขึ้นมา รับค่าจากตัวแปร ^^' งง
string return = cmdStored.Parameters["@ReturnValue"].Value.ToString();
ทำไม
Msg 245, Level 16, State 1, Procedure BACKUP_SELENE, Line 40Conversion failed when converting the varchar value '-1aaa' to data type int.- ข้อผิดพลาดด้านบนเกิดตอน execute procedure แต่ตอน compile ผ่านน่ะ
- จากข้อความ error ใน procedure ทำการ return '-1aaa' กลับ ซึ่งค่านี้ไม่สามารถแปลงเป็น int ได้
- แต่ถ้าเรา return 1 หรือ return '1' execute procedure แบบไม่มีปัญหาเลย
- ค่าที่จะ return ต้องเป็น int หรือ ค่าที่สามารถแปลงเป็น integer เท่านั้นหว่า
Notes:- แต่สมมุติเรา ทำการ execute procedure ใน app เรา และ นำตัวแปรมารับค่า
- int value = cmdStore.ExecuteNonQuery();
- value จะเป็นได้แค่ 1 กับ -1 procedure จะ return ค่าอาไรก็ชั่ง ค่าจะอิงจากผลของ statement ใน procedure เท่านั้น
- ถ้าใน procedure ไม่มี statement อาไรเกี่ยวกับ insert, update, delete ก็จะได้ค่ามาเป็น -1 ไง
- พูดถึงเรื่อง procedure แล้วก็อยากเอ๋ยถึง การสร้าง function ซะหน่อย คือ create function [name] เสร็จแล้วต้องตามด้วยวงเล็บ ( เปิด และ ปิด ) อ่ะ ไม่งั้น execute ไม่ผ่านหว่า อีกอย่างทำไม return ค่าเป็นประเภท double execute ไม่ผ่านซะงั้น
- เรื่องการ declare หรือ การประกาศตัวแปรนั้น ควรใช้หลักการของโปรแกรมมิ่งทั่วไป
Store error
can't declare repeat param @a same scope or not same scope
if ......1
begin
declare @a bit
set @a = 1
end
if .......2
begin
declare @a bit
set @a = 2
end
Store can execute but not recommended
if ......1
begin
declare @a bit
set @a = 1
end
if .......2
begin
set @a = 2
end
Store recommended
declare @a bit
if ......1
begin
set @a = 1
end
if .......2
begin
set @a = 2
end
References:
+Writing+Audio+CD+-+K3b.png)
