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)
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 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()
计算十五日平均值
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表的值从1开始
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')