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)) as begin SET NOCOUNT ON declare @billingku nvarchar(30), @billingbiao nvarchar(30), @billingall nvarchar(max) declare @...

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

create TRIGGER trigger_insert_test1 ON test2 AFTER insert AS BEGIN – 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) tableName from sys.dm_tran_locks where resource_type=‘OBJECT’ 解锁： declare @spid int Set @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] GO CREATE 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 rows from OJDT

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

固定格式with as () 1个子表 with aa 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...