//
you're reading...
Database, SQL Server

MsSQL Server Stored Procedure, declare variable and cursor recordset

How do set variable and set value variable in MsSQL Server Stored Procodure? let’go to these example :

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[sp_importCust]    Script Date: 12/01/2011 22:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[sp_importCust]
@parSessionID VARCHAR(16)


AS

DECLARE
@cif  varchar(255),
@cust_name     varchar(255),
@join_date     varchar,
@sales_code     varchar(255),
@sales_name     varchar(255),
@cust_type     varchar(25),
@sol_id     varchar(5),
@bu varchar(100),
@bool numeric(10)


SET NOCOUNT ON
/*– declare the cursor*/

DECLARE Cust CURSOR FOR
SELECT    cif, CUST_NAME,join_date,sol_id,sales_code,sales_name,bu,cust_type FROM tbl_cust_tmp
where session_id=@parSessionID

OPEN Cust
FETCH Cust INTO @cif, @cust_name,@join_date,@sol_id,@sales_code,@sales_name,@bu,@cust_type
WHILE @@Fetch_Status = 0
BEGIN
set @bool=0
set @bool = (select COUNT(*) from tbl_cust
where CIF=@cif)
if @bool < 1
insert into tbl_cust(cif, CUST_NAME,join_date,sol_id,sales_code,sales_name,bu,cust_type,inputter)
SELECT   cif, CUST_NAME,join_date,sol_id,sales_code,sales_name,bu,cust_type,session_id FROM tbl_cust_tmp
where session_id=@parSessionID and CIF=@cif

update tbl_cust set
CUST_NAME=@cust_name,join_date=@join_date,sol_id=@sol_id,
sales_code=@sales_code,sales_name=@sales_name,bu=@bu,cust_type=@cust_type
where CIF=@cif

FETCH Cust INTO @cif, @cust_name,@join_date,@sol_id,@sales_code,@sales_name,@bu,@cust_type

END
delete from tbl_cust_tmp where SESSION_ID =@parSessionID
CLOSE Cust
DEALLOCATE Cust

return
end

starting declare variable:

DECLARE
@cif  varchar(255),
@cust_name     varchar(255),

you can set value in variable with this code:

set @bool=0

set cursor recordset

OPEN Cust
FETCH Cust INTO @cif, @cust_name,@join_date,@sol_id,@sales_code,@sales_name,@bu,@cust_type
WHILE @@Fetch_Status = 0

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a comment

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 10 other subscribers
December 2011
M T W T F S S
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 187,115 hits