我的sql -- 非常实用

--查询的逻辑执行过程，来自技术内幕 
(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list> 
(1)  FROM <left_table> 
(3)    <join_type> JOIN <right_table> 
(2)      ON <join_condition> 
(4)  WHERE <where_condition> 
(5)  GROUP BY <group_by_list> 
(6)  WITH {CUBE | ROLLUP} 
(7)  HAVING <having_condition> 
(10) ORDER BY <order_by_list> 

http://www.hz2004.com.cn/post/SQL-String-Array.html

一、按指定符号分割字符串，返回分割后的元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。
CREATE function Get_StrArrayLength
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10)  --分隔符号
)
returns int
as
 begin
  declare @location int
  declare @start int
  declare @length int
  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
   while @location<>0
     begin
      set @start=@location+1
      set @location=charindex(@split,@str,@start)
      set @length=@length+1
     end
   return @length
 end

调用示例：select dbo.Get_StrArrayLength('78,1,2,3',',')

返回值：4


二、按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样方便

CREATE function Get_StrArrayStrOfIndex
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10),  --分隔符号
 @index int --取第几个元素
)
returns varchar(1024)
as
begin
 declare @location int
 declare @start int
 declare @next int
 declare @seed int
 set @str=ltrim(rtrim(@str))
 set @start=1
 set @next=1
 set @seed=len(@split)
 set @location=charindex(@split,@str)
 while @location<>0 and @index>@next
   begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
   end
 if @location =0 select @location =len(@str)+1 

--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
 return substring(@str,@start,@location-@start)
end

调用示例：select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2)

返回值：9



三、结合上边两个函数，象数组一样遍历字符串中的元素

 declare @str varchar(50)
 set @str='1,2,3,4,5'
 declare @next int  
 set @next=1
 while @next<=dbo.Get_StrArrayLength(@str,',')
   begin
     print dbo.Get_StrArrayStrOfIndex(@str,',',@next)
     set @next=@next+1
   end

 调用结果：
1
2
3
4
5

USE [PMS_ALL]
GO
/****** Object:  StoredProcedure [wo].[proc_get_ServiceSimpleSearch]    Script Date: 07/20/2009 10:59:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- =============================================
-- Author:            Zhengguoqing
-- Create Date:        20071019
-- 
-- ============================================= 
ALTER PROCEDURE [wo].[proc_get_ServiceSimpleSearch]
    @Code nvarchar(200) = null,--    Routes.Code
    @Description nvarchar(2000) = null,
    @LocalDescription nvarchar(2000) = null,
    @SiteCode varchar(5) = null,
    @BuildingCode varchar(5) = null,
    @status char(1),
    @Ordertype varchar(5),
    @CurrentPageNo INT OUTPUT,
    @CurrentPageSize INT,
    @TotalPageNo INT OUTPUT,
    @Order CHAR(4)
    WITH EXECUTE AS 'dbo'
AS 
    SET NOCOUNT ON ;
    set @Code = Isnull(Rtrim(Ltrim(@Code)), '')
    set @Description = Isnull(Rtrim(Ltrim(@Description)), '')
    set @LocalDescription = Isnull(Rtrim(Ltrim(@LocalDescription)), '')
    set @SiteCode = Isnull(Rtrim(Ltrim(@SiteCode)), '')
    set @BuildingCode = Isnull(Rtrim(Ltrim(@BuildingCode)), '')
    set @status = Isnull(Rtrim(Ltrim(@status)), '')
    set @Ordertype = Isnull(Rtrim(Ltrim(@Ordertype)), '')

    DECLARE @sqlCommandString NVARCHAR(MAX)

    DECLARE @BeginRowIndex INT
    DECLARE @EndRowIndex INT
    DECLARE @Flag CHAR(1)
    SET @Flag = 'N'

    DECLARE @TotalCount INT

    SET @BeginRowIndex = ( ( @CurrentPageNo - 1 ) * @CurrentPageSize ) + 1
    SET @EndRowIndex = @BeginRowIndex + @CurrentPageSize - 1

    SELECT  @TotalCount = COUNT(DISTINCT ServiceID)
    from    [wo].services(NOLOCK)
    where   ( @Code = ''
              or Code like @Code
            )
            and ( @Description = ''
                  or Description like @Description
                )
            and ( @LocalDescription = ''
                  or LocalDescription like @LocalDescription
                )
            and ( @SiteCode = ''
                  or SiteCode = @SiteCode
                )
            and ( @BuildingCode = ''
                  or BuildingCode = @BuildingCode
                )
            and ( @status = ''
                  or Status = @status
                )
            and ( @Ordertype = ''
                  or OrderType = @Ordertype
                )

    SET @TotalPageNo = CEILING(( @TotalCount * 1.0 ) / @CurrentPageSize) 
    IF @TotalPageNo = 0 
        BEGIN
            SET @BeginRowIndex = 0
            SET @EndRowIndex = 0
            SET @CurrentPageNo = 1
            SET @TotalPageNo = 1
        END
    ELSE 
        IF @CurrentPageNo > @TotalPageNo 
            BEGIN
                SET @CurrentPageNo = @TotalPageNo
                SET @BeginRowIndex = ( ( @CurrentPageNo - 1 )
                                       * @CurrentPageSize ) + 1
                SET @EndRowIndex = @BeginRowIndex + @CurrentPageSize - 1
            END

    IF @BeginRowIndex = @EndRowIndex
        AND @BeginRowIndex = 0 
        SET @EndRowIndex = @CurrentPageSize

    SET @sqlCommandString = ''
    SET @sqlCommandString = @sqlCommandString + 'SELECT * FROM ( '
    SET @sqlCommandString = @sqlCommandString
        + 'SELECT ROW_NUMBER() OVER (ORDER BY ServiceID ' + @Order
        + ')  AS RowNumber, '
    SET @sqlCommandString = @sqlCommandString + ' * 
from [wo].services  where 
(@Code='''' or Code like @Code)
and   (@Description='''' or Description like @Description)
and   (@LocalDescription='''' or LocalDescription like @LocalDescription )
and   (@SiteCode='''' or SiteCode = @SiteCode )
and   (@BuildingCode='''' or BuildingCode = @BuildingCode )
and   (@status='''' or Status = @status )
and    (@Ordertype='''' or OrderType=@Ordertype)'
    SET @sqlCommandString = @sqlCommandString + ') AS BinItems WHERE '
    SET @sqlCommandString = @sqlCommandString
        + ' RowNumber BETWEEN @BeginRowIndex AND @EndRowIndex '



    EXEC SP_EXECUTESQL @sqlCommandString, N'
@Code nvarchar(200)=null,
@Description nvarchar(2000)=null,
@LocalDescription nvarchar(2000)=null,
@SiteCode varchar(5)=null,
@BuildingCode varchar(5)=null,
@status char(1),
@BeginRowIndex int,
@EndRowIndex int,
@Ordertype varchar(5)', @Code, @Description, @LocalDescription, @SiteCode,
        @BuildingCode, @status, @BeginRowIndex, @EndRowIndex, @Ordertype


    SET NOCOUNT OFF

EXEC sp_changeobjectowner 'a0501233515.Orders', 'dbo'

DECLARE @id int

DECLARE snowleopard CURSOR 
FOR SELECT  staffID FROM  wo.Staff

OPEN snowleopard

FETCH NEXT FROM snowleopard INTO @id
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT * FROM  wo.staff WHERE   staffID = @id
        FETCH NEXT FROM snowleopard INTO @id
    END

CLOSE snowleopard
DEALLOCATE snowleopard

C#

string siteCode = "";
if (ddlSite.SelectedValue.Length == 0)
    foreach (ListItem li in ddlSite.Items)
        siteCode += "," + li.Value;
else
    siteCode = ddlSite.SelectedValue;

Sql

DECLARE @codelist VARCHAR(max)
SET @codelist = ',0125,4444,55,'
SET @codelist = ',' + ISNULL(LTRIM(RTRIM(@codelist)), '') + ','

SELECT * FROM wo.PMs
WHERE CHARINDEX(',' + Code + ',', ',' + @codelist + ',') > 0

处理表重复记录(查询和删除)
查询: 内容相同的行只保留ID最小/大的一行

select * from #T a where ID=(select min(ID) from #T where Name=a.Name)
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) 

删除: 内容相同的行只保留ID最小/大的一行

delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)




参考:

{

--处理表重复记录(查询和删除)
/**//******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go


--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

生成结果:
/**//*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

生成结果2:
/**//*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/



--2、删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)

方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)



select * from #T

生成结果:
/**//*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID保留最大的一条记录:

方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
delete a  from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)

方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)


select * from #T
/**//*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/





--3、删除重复记录没有大小关系时，处理重复值


--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go

方法1:
if object_id('Tempdb..#') is not null
    drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#

truncate table #T--清空表

insert #T select * from #    --把临时表#插入到表#T中

--查看结果
select * from #T

/**//*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:

alter table #T add ID int identity--新增标识列
go
delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列

--查看结果
select * from #T

/**//*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin 
    set rowcount @con;
    delete #T where Num=@Num and Name=@Name
    set rowcount 0;
    fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor

--查看结果
select * from #T
/**//*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

}

if not object_id('TableName') is null
    drop table TableName

create table tb(Name int ,Type varchar(10), YN varchar(10))
insert into tb values(1 ,   'A' ,   'Y') 
insert into tb values(3 ,   'B' ,   'Y') 
insert into tb values(4 ,   'B' ,   'Y' )
insert into tb values(3 ,   'A' ,   'N') 
insert into tb values(4 ,   'A' ,   'Y') 
insert into tb values(4 ,   'B' ,   'Y') 
insert into tb values(1 ,   'C' ,   'N') 
go
--sql 2000静态语句，指TYPE+YN的组合固定
select name ,
  sum(case when type = 'A' and yn = 'Y' then 1 else 0 end ) [A&Y],
  sum(case when type = 'A' and yn = 'N' then 1 else 0 end ) [A&N],
  sum(case when type = 'B' and yn = 'Y' then 1 else 0 end ) [B&Y],
  sum(case when type = 'B' and yn = 'N' then 1 else 0 end ) [B&N],
  sum(case when type = 'C' and yn = 'Y' then 1 else 0 end ) [C&Y],
  sum(case when type = 'C' and yn = 'N' then 1 else 0 end ) [C&N]
from tb
group by name
/*
name        A&Y         A&N         B&Y         B&N         C&Y         C&N         
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           1           0           0           0           0           1
3           0           1           1           0           0           0
4           1           0           2           0           0           0

（所影响的行数为 3 行）
*/

--sql 2000动态语句，指TYPE+YN的组合不固定
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , sum(case when type = ''' + type + ''' and yn = ''' + yn + ''' then 1 else 0 end) [' + type + '&' + yn + ' ]'
from (select distinct type , yn from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql) 
/*
name        A&N         A&Y         B&Y         C&N         
----------- ----------- ----------- ----------- ----------- 
1           0           1           0           1
3           1           0           1           0
4           0           1           2           0
*/
drop table tb

public static void GetSqlError()
{
    try
    {
        string conString = "server=(local);uid=sa;pwd=sql;database=test1";
        string sql = "select * from t1";
        string raisError = "RAISERROR('Rais Error1', 16, 1) WITH NOWAIT";
        sql = sql + ";" + raisError;
        SqlConnection con = new SqlConnection(conString);
        SqlCommand cmd = new SqlCommand(sql, con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}



C# catch 块中可以俘获 SQL Server 抛出的自定义异常（RAISERROR） 
在 SQL Server 的存储过程中根据业务逻辑的要求，有时需要抛出自定义异常，由C#程序俘获之并进行相应的处理。
SQL Server 抛出自定义异常和简单，像这样就可以了：RAISERROR('Rais Error1', 16, 1) WITH NOWAIT
但定用什么样的错误级别却很讲究，否则 C# 中的 catch 块可能俘获不到的。

SQL Servr 抛出自定义异常的方法一般这么写就可以：
RAISERROR('Rais Error1', 16, 1) WITH NOWAIT

其中数字 16 代表错误级别：
错误级别从 0 到 25， 其中 19 到 25 是重大错误级别。
注：小于 0 的级别被解释为 0，大于 25 的级别被解释为 25。 

任何用户都可以指定 0 到 18 之间的错误级别。
19 到 25 级别的错误，只能由 sysadmin 角色的成员用 WITH LOG 选项指定。
19 到 25 级别的错误，将记录到错误日志和应用程序日志。
20 到 25 级别的错误，被认为是致命的；遇到致命的级别错误，客户端的连接将在收到消息后终止。

C# 中的 catch 块中可以俘获 错误级别 11 到 19 级别的异常。
0 到 10 级别的错误根本不进catch 块；而 20 到 25 级别的异常，被认为是致命的，会被断开数据库连接。所以 C# cath 块可以接受到这种异常，
但异常的内容不是真正的异常内容，可能是类似这样的内容：“在从服务器接收结果时发生传输级错误”。

修改Schema

ALTER SCHEMA wo TRANSFER dbo.procname

declare   @str   varchar(8000)   
  set   @str   =   ''   
  select   @str   =   @str   +   ','   +   name   From   sysobjects   WHERE   xtype='P'   and   status>=0   
  select   @str   =   'drop   proc   '   +   substring(@str,2,8000)   
  select   @str    
  exec   (@str) 

--执行即可

 

问题描述: 
创建数据库时一般使用sa在本机创建, schema为dbo
而部署到实际机器时, 一般只有普通权限, schema为登陆名
这为同步时带来了一些困难.

解决方案:

1.使用SQLDelta生成脚本 (注意选项)

 
2.将schema全部替换为空, 让目标数据库自动生成

 

3.利用MSSQL自带的工具导出数据, 注意修改目标的schema, 以及勾选插入标识列

 

 

1。在SQL Server 2005中批量修改存储过程的架构,执行以下SQL，将执行结果拷贝出来，批量执行既可。

SELECT 'ALTER SCHEMA 新的架构名称 TRANSFER ' + s.Name + '.' + p.Name 
FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id 
WHERE s.Name = '旧的架构名称'

2。在SQL Server 2005中批量修改表的架构,执行以下SQL，将执行结果拷贝出来，批量执行既可。

declare @name sysname
declare csr1 cursor
for 
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
open csr1

FETCH NEXT FROM csr1 INTO @name
while (@@FETCH_STATUS=0)
BEGIN
SET @name='旧的架构名称.' + @name

print 'ALTER SCHEMA 新的架构名称 TRANSFER ' + @name
fetch next from csr1 into @name
END
CLOSE csr1
DEALLOCATE csr1

然后将个结果在执行一遍即可。