isql(sybase) 语法总结

Transact-SQL(sybase sqlserver)

SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，但后来随着两公司的合作结束，他们分别在自己的版本中加入了不同的扩展功能。

replace(@Country,'|', ''',''')|为','declare @sql varchar(5000)

select @sql = 'select '+ @param+'from....'

在拼字符串时，注意类型，类型不一致　@sql就为空

cast(cast('193.57' AS float) as int)
cast('193' AS int)
 convert(int,'10',113)

charindex('Delta1', @business) > 0
convert(varchar(20),@count)=str(@count)
convert(date,@date)=date(@date)
实际的sql:convert(date,str(year)+'-'+str(month)+'-01')>'01/01/2011'
isql中拼字符串'convert(date,str(year)+''-''+str(month)+''-01'')>'''+dateformat(@startDate,'mm/dd/yyyy')'''

dateadd(dd,1, @tmpDate)
datepart(dy, getDate())
datediff(millisecond, @startTime, @endTime)

日期分量 缩写 值
Year yy 0001 – 9999
Quarter qq 1 – 4
Month mm 1 – 12
Week wk 1 – 54
Day dd 1 – 31
Dayofyear dy 1 – 366
Weekday dw 1 – 7 （周日– 周六）
Hour hh 0 – 23
Minute mi 0 – 59
Second ss 0 – 59
Millisecond ms 0 – 999

ISDATE  ISNUMERIC NOW

select isnull(p.pnl,0) pnl ,其他列中  和where,group,order by可以使用
select top 5 gpnum,isnull(segment,' ') segment from TblGeriTradesDateRange_rollup where segment is null
select top 5 gpnum,segment from TblGeriTradesDateRange_rollup where segment is null

case when ... then ... else .... end
if(...)begin...end else begin....end
while(...) begin....end


CREATE PROCEDURE spGetDateCommon(
    @sStartDate    varchar(30) = 'YTD' ,
    @sEndDate      varchar(30)='YTD' ,
    @startDate     date OUTPUT,
    @endDate       date OUTPUT
)

表字段默认为　not null

LastModifiedBy   varchar(10) default 'BATCH',
LastModifiedOn  datetime default getdate() 
计算十五日平均值

create table #tmpTable(
      rowid int identity,
      dt  varchar(100),
      TotalTurnoverUSD float null,
      GrossCommissions float null,
      MAVG        float null
    )
    declare @tmpDate date
    select @tmpDate=date('2012/01/01')
    while(@tmpDate <= date('2012/07/01'))
    begin
    if(datepart(dw,@tmpDate) > 1 and datepart(dw,@tmpDate) < 7)
    insert into #tmpTable( dt,TotalTurnoverUSD,GrossCommissions) values(DATEFORMAT(@tmpDate,'dd/mm/yyyy'),0,0)
    select @tmpDate = dateadd(dd,1, @tmpDate)
    end   

    select  * into #TblAvg from #tmpTable t  where t.dt >=@startDate and t.dt <=@endDate
    update #TblAvg t set t.rowid=t.rowid-(select min(rowid) from #TblAvg)+1  进入#TblAvg表的值从１开始  
    update #TblAvg t set t.GrossCommissions=(select GrossCommissions from rollup  where dt=dateformat(t.dt,'dd/mm/yyyy'))   

    update #TblAvg t set MAVG=((select sum(GrossCommissions) from #TblAvg where rowid>(case  when t.rowid>15 then t.rowid-15 else 0 end) and rowid<=t.rowid))/(case  when t.rowid/15=0 then rowid else 15 end )

EXISTS(select * from SYSPROCEDURE where   proc_name='someSP')

OBJECT_ID('someTable')

declare @sql varchar(1000),@drop varchar(100)
set @sql='select index_name into #tmp from sysindex i,sysusers u where i.index_name='''+someIndex+''' and u.name='''+@schema+''' and i.creator=u.uid'
execute(@sql)
exists(select * from #tmp)

下面的还包含schema,但需要管理员权限
sp_iqindex TblClientInfo,NULL,avita
sp_iqtable NULL,avita
sp_iqprocedure NULL,avita

select index_name  from sysindex i,systable t where i.table_id=t.table_id  and t.table_name='TblGeriResearch_rollup'
select * from SYSPROCEDURE p,sysuses u where  p.creator=u.uid and  p.proc_name='spGetClientCommissionOverview' and u.name='avita'

grant execute/select on getAvitaCntry to avita
select t.table_name,p.selectauth from SYSTABLEPERM p,systable t
where p.grantor='104' and p.grantee='160'
and p.ttable_id=t.table_id

select userid,number_connects from IQ_USER_LOGIN_INFO_TABLE where userid in ('sales','cp_dbo','avita')