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)

--테이블 목록 가져오기:
SELECT * FROM sysobjects WHERE xtype='U' 

--뷰:
SELECT * FROM sysobjects WHERE xtype='V' 

--프로시져:
SELECT * FROM sysobjects WHERE xtype='P' 


--테이블 리스트
SELECT * FROM  INFORMATION_SCHEMA.TABLES
--뷰 리스트
SELECT * FROM  INFORMATION_SCHEMA.VIEWS
--컬럼 리스트
SELECT * FROM  INFORMATION_SCHEMA.COLUMNS
--컬럼 키값
SELECT * FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE

--프로시져
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
--프로시져 파라메타값
SELECT * FROM  INFORMATION_SCHEMA.PARAMETERS

 

--컬럼 정보 가져오기
SELECT
 A.TABLE_CATALOG
 ,A.TABLE_NAME
 ,A.ORDINAL_POSITION
 ,A.COLUMN_NAME
 ,A.DATA_TYPE
 ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH,'')
 ,ISNULL(A.NUMERIC_PRECISION,'')
 ,A.IS_NULLABLE
 ,ISNULL(A.COLUMN_DEFAULT,'')
 ,ISNULL(B.CONSTRAINT_NAME,'')
 ,ISNULL(A.CHARACTER_SET_NAME,'')
 ,ISNULL(A.COLLATION_NAME,'')
 ,CASE WHEN ISNULL(C.NAME,'') = '' THEN '' ELSE 'Identity' END auto
FROM 
 INFORMATION_SCHEMA.COLUMNS A
 LEFT OUTER JOIN
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
 ON A.TABLE_NAME = B.TABLE_NAME 
 AND A.COLUMN_NAME = B.COLUMN_NAME
 LEFT OUTER JOIN
 syscolumns C 
 ON C.ID = object_id(A.TABLE_NAME) AND A.COLUMN_NAME = C.NAME AND C.COLSTAT & 1 = 1 
WHERE
 A.TABLE_NAME = '테이블명'
ORDER BY A.ORDINAL_POSITION