'프로시져'에 해당되는 글 3건

ALTER PROCEDURE [dbo].[proc_Account] 
	@yymm  CHAR(6),
	@flag  VARCHAR(10)
AS
	DECLARE @sql varchar(5000)
	DECLARE @opt_price INT
	--SET @opt_price=(select isnull(sum(add_inDanga+((su-1)*add_optDanga*add_inDanga)),0) from Order_detail OD, order_options OO where OD.seq=OO.seq and OD.num=OO.num and OD.buy_code=@yymm  + '-' + char(39) + ')
	SET @sql='select Account_code, Account_no, '
IF @flag='buy' BEGIN
	SET @sql=@sql +'(select buyCo_nameC from tbl_buyCo where buyCo_id=tbl_Account.Account_co) as make_co_name, 0 as co_tax, '
END
ELSE	BEGIN
	SET @sql=@sql +'case when Account_co=0 then ' + char(39) + '이미지사이트' + char(39) +  ' when Account_co<>0 then (select custom_name from dbo.other_order where custom_code=tbl_Account.Account_co) end as make_co_name, '
	SET @sql=@sql +'isnull((select co_tax from other_order where custom_code=tbl_Account.Account_co),0) as co_tax, '
END
	SET @sql=@sql + 'Account_st, Account_en, '
IF @flag='buy' BEGIN
	SET @sql=@sql + ' (isnull((select sum(case order_detail.ea_box when 0 then order_detail.in_danga*order_detail.su when 1 then order_detail.in_danga*order_detail.su*order_detail.box_su end) from order_detail,[order] where buy_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no) and order_detail.seq=[order].seq and ( (left([order_detail].seq,1)<>' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag in(70,80)) or  (left([order_detail].seq,1)=' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag>30) ) ),0))+(select isnull(sum(add_inDanga+((su-1)*add_optDanga*add_inDanga)),0) from Order_detail OD, order_options OO where OD.seq=OO.seq and OD.num=OO.num and OD.buy_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no) ) as Account_price '
END
ELSE	BEGIN
	SET @sql=@sql + '((select sum(case when [order].custom_code=0 then total_pay when [order].custom_code<>0 then custom_danga*su end) from order_detail,[order] where order_detail.seq=[order].seq and sell_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no) and  ((left([order_detail].seq,1)<>' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag in(70,80)) or  (left([order_detail].seq,1)=' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag>30) ) )+(select isnull(sum(add_sellDanga+((su-1)*add_optDanga*add_sellDanga)),0) from Order_detail OD, order_options OO where OD.seq=OO.seq and OD.num=OO.num and OD.sell_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no)) ) as Account_price '
END
	SET @sql=@sql + ', Convert(varchar(10),Account_date,112) as Account_date,  Account_price as Account_price2, receive_pay, aFlag, (select Isnull(sum(Account_in-Account_out),0) from tbl_inOutPay where Account_idx=' + char(39) +@yymm + '-' + char(39) + '+Convert(varchar(10), Account_no)) as sum_pay, Account_memo, Account_tax, apply '
	SET @sql=@sql + ', (select top 1 Summary from tbl_inOutPay where Account_idx= tbl_Account.Account_code + ' + Char(39) + '-' + Char(39) + ' + Convert(varchar(3),Account_no) and Account in(1,2)) as summary'
	SET @sql=@sql + ' from tbl_Account '
	SET @sql=@sql + ' where Account_flag=' + char(39) + @flag + char(39) + ' and Account_code=' + @yymm
	SET @sql=@sql + ' order by Account_No asc'
	--select @sql
	exec(@sql)

ALTER PROCEDURE [dbo].[get_goods_search] 
	@sang 		varchar(20),
	@userIP 	varchar(20)
AS
	set nocount on
	declare  @sql varchar(5000)
	SET @sang=replace(@sang,' ','')
	
	insert SearchHit(searchS,userIP) values (left(@sang,25), @userIP)
	if len(@sang)>=2 BEGIN
		set @sql = 'select sang_name,brand,code,ea_danga,box_danga,box_su,simage, icon, (case when Convert(varchar(10),indate,112)>=Convert(varchar(10),getdate()-30,112) then 1 else 0 end) as New_icon from goods where hidden_flag=''0'' and  ((brand>0 and brand<1000) or brand=20) and '
		set @sql = @sql + ' (replace(sang_name,'+char(39) + ' ' + char(39) + ','+char(39)+char(39)+') like ' + char(39) + '%' + @sang + '%'  + char(39)  + ' or replace(keyword,'+char(39) + ' ' + char(39) + ','+char(39)+char(39)+') like ' + char(39) + '%' + @sang + '%'  + char(39)  + ' or goods_code like ' + char(39) + '%' + @sang + '%'  + char(39) +') or Convert(varchar(7),code)=' + char(39) + @sang + char(39) + ' Order by seq, Code Desc'
	END
	set nocount off
--select @sql
exec(@sql)

DBLINK

1. sa 계정으로 sql 스튜디오에 접속한다.

2. 원격 DB링크 연결

-> exec sp_addlinkedserver @server='생성할디비링크명', @srvproduct='', @provider='SQLOLEDB', @datasrc='접속할디비아이피'

3. 원격 DB에 로그인 허용

-> exec sp_addlinkedsrvlogin @rmtsrvname='DB 링크명', @useself='False', @locallogin=Null, @rmtuser='계정명', @rmtpassword='비밀번호';

4. 원격 DB의 프로시져 호출

-> exec 링크명.데이터베이스명.계정명.프로시저명;

5. 만약 RPC에 대해 구성되지 않았습니다 라는 오류가 뜬다면 아래 설정을 바꿔준다.

연결된서버로부터 로컬서버로의 RPC 호출을 활성화

EXEC sp_serveroption 'DB 링크명', 'rpc', TRUE;

6. 로컬서버로부터 연결된서버로의 RPC 호출을 활성화

EXEC sp_serveroption 'DB 링크명', 'rpc out', TRUE;