//
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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

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

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,551 hits
%d bloggers like this: