ตัวอย่าง Store Procedure SQLServer

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 40
Conversion 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:

• http://geekswithblogs.net/NewThingsILearned/archive/2008/09/17/get-return-value-from-stored-procedure.aspx
• http://www.windows-tech.info/15/0c9dbf40376124b0.php
• http://www.experts-exchange.com/Database/DB2/Q_24067694.html
• http://forums.asp.net/t/1375567.aspx
• http://www.bloggang.com/
• http://www.codeguru.com/article-preview.php/15219/