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
Discussion
No comments yet.