ko_oi的博客

declare @id int=1,@maxid int,@ku nvarchar(20),@dbname nvarchar(50), @sql nvarchar(max)select @maxid=count(1) from (select row_number()over(order by companycode) as id ,* from Company) company...

alter proc billdownexpress (@billid nvarchar(30))asbegin SET NOCOUNT ON declare @billingku nvarchar(30), @billingbiao nvarchar(30), @billingall nvarchar(max) declare @...

USE [SapAllData]GOCREATE proc [dbo].[p_cashflow_c](@dbname varchar(50) ,@stime varchar(10) ,@etime varchar(10))asdeclare @stimeb varchar(10),@stimec varchar(10)set @stimeb=(select dateadd...

create TRIGGER trigger_insert_test1ON test2AFTER insertASBEGIN– SET NOCOUNT ON added to prevent extra result sets from– interfering with SELECT statements.SET NOCOUNT ON;insert into test1 sel...

在本文中，GetDate()获得的日期由两部分组成，分别是今天的日期和当时的时间： Select GetDate() 用DateName()就可以获得相应的年、月、日，然后再把它们连接起来就可以了：Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())另外，DateN...

if exists和if not exists关键字用法1.介绍 　　if not exists 即如果不存在，if exists 即如果存在2.使用 　　a.判断数据库不存在时 　　if not exists（select * from sys.databases where name = ‘database_name’）　　b.判断表不存在时 　　if not exists (s...

CREATE PROCEDURE dbname.proc_getGrade (stu_no varchar(20),cour_no varchar(10)) BEGIN DECLARE stu_grade float; SELECT grade into stu_grade FROM grade WHERE student_no=stu_no AND course_no=cour_...

查看sqlserver被锁的表：select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type=‘OBJECT’解锁：declare @spid intSet @spid = 358 --锁表进程de...

select t1.AcctCode,t1.AcctName,t0.shortname,t3.cardname,sum(case when t2.RefDate<'[%0]' then t0.Debit-t0.Credit else 0 end ) as 期初余额,sum(case when t2.RefDate >='[%0]' and t2.RefDate <= '[%1...

USE [SapAllData]GOCREATE PROCEDURE [dbo].[PK_Test]AS --声明1个变量 declare @O_company nvarchar(20) --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同 declare mycursor cursor for select dbname from ...

select refdate,left(convert(varchar,refdate,112),6),transid,ROW_NUMBER() over(partition by left(convert(varchar,refdate,112),6) order by RefDate ,TransId ) as rowsfrom OJDT

1.查询数据库中的所有数据库名：SELECT Name FROM Master..SysDatabases ORDER BY Name2.查询某个数据库中所有的表名：SELECT Name FROM SysObjects Where XType='U' ORDER BY Name...

固定格式with as ()1个子表withaa as (select transid from JDT1 where account =‘100303’ )select TransId into #b from jdt1 where TransId in (select * from aa)多个子表with aa as (select a from test1 where a=‘3...