精典SQL語句

　  從上面的結果，我們可以清楚的看到，一筆的漢字，code是從19968到20101，從小到大排，但到
了二筆漢字的第一個字“丁”，CODE為19969，就不按順序而重新開始了。有了這結果，我們就可以輕
松的用SQL語句得到每種筆劃漢字歸類的第一個或最後一個漢字。
下面用語句得到最後一個漢字：

create table #t1(id int identity,code int,cnWord nvarchar(2))

insert #t1(code,cnWord)
select code,nchar(code) as CNWord  from #t
order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code

select A.cnWord
from #t1 A
left join #t1 B on A.id=B.id-1 and A.code<B.code
where B.code is null
order by A.id

得到36個漢字，每個漢字都是每種筆劃數按Chinese_PRC_Stroke_CS_AS_KS_WS排序規則排序後的
最後一個漢字：

亅阝馬風龍齊龜齒鸩龀龛龂龆龈龊龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘

　　上面可以看出：“亅”是所有一筆漢字排序後的最後一個字，“阝”是所有二筆漢字排序後的最後
一個字......等等。
　　但同時也發現，從第33個漢字“龗(33筆)”後面的筆劃有些亂，不正確。但沒關系，比“龗”筆劃
多的只有四個漢字，我們手工加上：齾35筆，齉36筆，靐39筆，龘64筆

建漢字筆劃表（TAB_HZBH）：
create table tab_hzbh(id int identity,cnWord nchar(1))
--先插入前33個漢字
insert tab_hzbh
select top 33 A.cnWord
from #t1 A
left join #t1 B on A.id=B.id-1 and A.code<B.code
where B.code is null
order by A.id
--再加最後四個漢字
set identity_insert tab_hzbh on
go
insert tab_hzbh(id,cnWord)
　　　　　select 35,N''齾''
union all select 36,N''齉''
union all select 39,N''靐''
union all select 64,N''龘''
go
set identity_insert tab_hzbh off
go

　　到此為止，我們可以得到結果了，比如我們想得到漢字“國”的筆劃：

declare @a nchar(1)
set @a=''國''
select top 1 id
from  tab_hzbh
where cnWord>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WS
order by id

id         
-----------
8
(結果：漢字“國”筆劃數為8)

　　上面所有准備過程，只是為了寫下面這個函數，這個函數撇開上面建的所有臨時表和固
定表，為了通用和代碼轉移方便，把表tab_hzbh的內容寫在語句內，

create function fun_getbh(@str nvarchar(4000))
returns int
as
begin
declare @Word nchar(1),@n int
set @n=0
while len(@str)>0
begin
set @Word=left(@str,1)
--如果非漢字，筆劃當0計
set @n=@n+(case when unicode(@Word) between 19968 and 19968+20901
then (select top 1 id from (
select 1 as id,N''亅'' as Word
union all select 2,N''阝''
union all select 3,N''馬''
union all select 4,N''風''
union all select 5,N''龍''
union all select 6,N''齊''
union all select 7,N''龜''
union all select 8,N''齒''
union all select 9,N''鸩''
union all select 10,N''龀''
union all select 11,N''龛''
union all select 12,N''龂''
union all select 13,N''龆''
union all select 14,N''龈''
union all select 15,N''龊''
union all select 16,N''龍''
union all select 17,N''龠''
union all select 18,N''龎''
union all select 19,N''龐''
union all select 20,N''龑''
union all select 21,N''龡''
union all select 22,N''龢''
union all select 23,N''龝''
union all select 24,N''齹''
union all select 25,N''龣''
union all select 26,N''龥''
union all select 27,N''齈''
union all select 28,N''龞''
union all select 29,N''麷''
union all select 30,N''鸞''
union all select 31,N''麣''
union all select 32,N''龖''
union all select 33,N''龗''
union all select 35,N''齾''
union all select 36,N''齉''
union all select 39,N''靐''
union all select 64,N''龘''
) T
where word>=@Word collate Chinese_PRC_Stroke_CS_AS_KS_WS
order by id ASC) else 0 end)
set @str=right(@str,len(@str)-1)
end
return @n
end

--函數調用實例：
select dbo.fun_getbh(''中華人民共和國''),dbo.fun_getbh(''中華人民共和國'')
　
　　執行結果：筆劃總數分別為39和46，簡繁體都行。

    當然，你也可以把上面“UNION　ALL”內的漢字和筆劃改存在固定表內，在漢字
列建CLUSTERED INDEX，列排序規則設定為：
　　　 Chinese_PRC_Stroke_CS_AS_KS_WS
這樣速度更快。如果你用的是BIG5碼的操作系統，你得另外生成漢字，方法一樣。
但有一點要記住：這些漢字是通過SQL語句SELECT出來的，不是手工輸入的，更不
是查字典得來的，因為新華字典畢竟不同於UNICODE字符集，查字典的結果會不正
確。

　　
          　　　　用排序規則的特性得到漢字拼音首字母

　　用得到筆劃總數相同的方法，我們也可以寫出求漢字拼音首字母的函數。如下：

create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @Word nchar(1),@PY nvarchar(4000)
set @PY=''''
while len(@str)>0
begin
set @Word=left(@str,1)
--如果非漢字字符，返回原字符
set @PY=@PY+(case when unicode(@Word) between 19968 and 19968+20901
then (select top 1 PY from (
select ''A'' as PY,N''驁'' as Word
union all select ''B'',N''簿''
union all select ''C'',N''錯''
union all select ''D'',N''鵽''
union all select ''E'',N''樲''
union all select ''F'',N''鰒''
union all select ''G'',N''腂''
union all select ''H'',N''夻''
union all select ''J'',N''攈''
union all select ''K'',N''穒''
union all select ''L'',N''鱳''
union all select ''M'',N''旀''
union all select ''N'',N''桛''
union all select ''O'',N''漚''
union all select ''P'',N''曝''
union all select ''Q'',N''囕''
union all select ''R'',N''鶸''
union all select ''S'',N''蜶''
union all select ''T'',N''籜''
union all select ''W'',N''鶩''
union all select ''X'',N''鑂''
union all select ''Y'',N''韻''
union all select ''Z'',N''咗''
) T
where word>=@Word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @Word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

--函數調用實例：
select dbo.fun_getPY(''中華人民共和國''),dbo.fun_getPY(''中華人民共和國'')
結果都為：ZHRMGHG

　  你若有興趣，也可用相同的方法，擴展為得到漢字全拼的函數，甚至還可以得到全拼的讀
音聲調，不過全拼分類大多了。得到全拼最好是用對照表，兩萬多漢字搜索速度很快，用對照
表還可以充分利用表的索引。
    排序規則還有很多其它的巧妙用法，限於篇幅在此就不再詳細說明。歡迎大家共同探討。
==================================================================================================
如何實現對數據庫單個字段進行加密     選擇自 callzjy 的 Blog 
關鍵字   callzjy 字段加密 sqlserver 函數
出處   
 
 create vIEw v_rand

as

select c=unicode(cast(round(rand()*255,0) as tinyint))

go

create function f_jmstr(@str varchar(8000),@type bit)returns varchar(8000)

/*

*參數說明

*str:要加密的字符串或已經加密後的字符

*type:操作類型--0加密--解密

*返回值說明

*當操作類型為加密時(type--0):返回為加密後的str,即存放於數據庫中的字符串

*當操作類型為解密時(type--1):返回為實際字符串,即加密字符串解密後的原來字符串

*/

As

begin

         declare @re varchar(8000)--返回值

         declare @c int--加密字符

         declare @i int

/*

*加密方法為原字符異或一個隨機ASCII字符

*/
    if @type=0--加密
    begin
                   select @c=c,@re='''',@i=len(@str) from v_rand
                   while @i>0
                       select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re
                                    ,@i=@i-1
                   set @re=@re+nchar(@c)
    end
    else--解密
    begin
                   select @i=len(@str)-1,@c=unicode(substring(@str,@i+1,1)),@re=''''
                   while @i>0
                            select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re ,@i=@i-1
         end
         return(@re)
end
go
--測試
declare @tempstr varchar(20)
set @tempstr=''  1 2   3aA''
select dbo.f_jmstr(dbo.f_jmstr(@tempstr,0),1)
輸出結果
  1 2   3aA
(完)
==================================================================================================
讓數據庫產生一張詳細的日歷表

也許有了這張表，你的工作會輕松很多！

CREATE TABLE [dbo].[time_dimension] (
   [time_id] [int] IDENTITY (1, 1) NOT NULL ,
   [the_date] [datetime] NULL ,
   [the_day] [nvarchar] (15) NULL ,
   [the_month] [nvarchar] (15) NULL ,
   [the_year] [smallint] NULL ,
   [day_of_month] [smallint] NULL ,
   [week_of_year] [smallint] NULL ,
   [month_of_year] [smallint] NULL ,
   [quarter] [nvarchar] (2) NULL ,
   [fiscal_period] [nvarchar] (20) NULL
) ON [PRIMARY]

DECLARE @WeekString varchar(12),
@dDate SMALLDATETIME,
@sMonth varchar(20),
@iYear smallint,
@iDayOfMonth smallint,
@iWeekOfYear smallint,
@iMonthOfYear smallint,
@sQuarter varchar(2),
@sSQL varchar(100),
@adddays int
 
SELECT @adddays = 1 --日期增量（可以自由設定）
SELECT @dDate = ''01/01/2002'' --開始日期
 
WHILE @dDate < ''12/31/2004''  --結束日期
BEGIN
 
   SELECT @WeekString = DATENAME (dw, @dDate)
   SELECT @sMonth=DATENAME(mm,@dDate)
   SELECT @iYear= DATENAME (yy, @dDate

   INSERT INTO time_dimension(the_date, the_day, the_month, the_year,
   day_of_month,
   week_of_year, month_of_year, quarter) VALUES
   (@dDate, @WeekString, @sMonth, @iYear, @iDayOfMonth, @iWeekOfYear,
   @iMonthOfYear, @sQuarter)
   SELECT @dDate = @dDate + @adddays
END
GO

select * from time_dimension

=================================================================================
 --搜索某個字符串在那個表的那個字段中
declare @str varchar(100)
set @str=''White''  --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s=''if exists(select 1 from [''+b.name+''] where [''+a.name+''] like ''''%''+@str+''%''''

declare @tbname sysname
set @tbname=''客戶資料''

declare @dbname sysname,@sql nvarchar(4000),@re bit,@sql1 varchar(8000)
set @sql1=''''
declare tb cursor for select name from master..sysdatabases
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
 set @sql=''set @re=case when exists(select 1 from [''
  +@dbname+'']..sysobjects where xtype=''''U'''' and name=''''''
  +@tbname+'''''') then 1 else 0 end''
 exec sp_executesql @sql,N''@re bit out'',@re out
 if @re=1 set @sql1=@sql1+'' union all select ''''''+@dbname+''''''''
 fetch next from tb into @dbname
end
close tb
deallocate tb
set @sql1=substring(@sql1,12,8000)
exec(@sql1)

======================================================================================
  比較兩個數據庫的表結構差異     選擇自 zjcxc 的 Blog 
關鍵字   表結構,差異
出處   
 
 /*--比較兩個數據庫的表結構差異
 
--*/
/*--調用示例
 
 exec p_comparestructure ''xzkh_model'',''xzkh_new''
--*/

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[p_comparestructure]'') and OBJECTPROPERTY(id,

N''IsProcedure'') = 1)
drop procedure [dbo].[p_comparestructure]
GO

create proc p_comparestructure
@dbname1 varchar(250), --要比較的數據庫名1
@dbname2 varchar(250) --要比較的數據庫名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序號 int,標識 bit,主鍵 bit,類型 varchar(250),
 占用字節數 int,長度 int,

create table #tb2(表名2 varchar(250),字段名 varchar(250),序號 int,標識 bit,主鍵 bit,類型 varchar(250),
 占用字節數 int,長度 int,小數位數 int,允許空 bit,默認值 varchar(500),字段說明 varchar(500))

--得到數據庫1的結構
exec(''insert into #tb1 SELECT
 表名=d.name,字段名=a.name,序號=a.colid,
 標識=case when a.status=0x80 then 1 else 0 end,
 主鍵=case when exists(SELECT 1 FROM ''+@dbname1+''..sysobjects where xtype=''''PK'''' and name in (
  SELECT name FROM ''+@dbname1+''..sysindexes WHERE indid in(
   SELECT indid FROM ''+@dbname1+''..sysindexkeys WHERE id = a.id AND colid=a.colid
  ))) then 1 else 0 end,
 類型=b.name, 占用字節數=a.length,長度=a.prec,小數位數=a.scale, 允許空=a.isnullable,
 默認值=isnull(e.text,''''''''''''),字段說明=isnull(g.[value],'''''''''''')
FROM ''+@dbname1+''..syscolumns a
 left join ''+@dbname1+''..systypes b on a.xtype=b.xusertype
 inner join ''+@dbname1+''..sysobjects d on a.id=d.id  and d.xtype=''''U'''' and  d.name<>''''dtpropertIEs''''
 left join ''+@dbname1+''..syscomments e on a.cdefault=e.id
 left join ''+@dbname1+''..syspropertIEs g on a.id=g.id and a.colid=g.smallid 
order by a.id,a.colorder'')

--得到數據庫2的結構
exec(''insert into #tb2 SELECT
 表名=d.name,字段名=a.name,序號=a.colid,
 標識=case when a.status=0x80 then 1 else 0 end,
 主鍵=case when exists(SELECT 1 FROM ''+@dbname2+''..sysobjects where xtype=''''PK'''' and name in (
  SELECT name FROM ''+@dbname2+''..sysindexes WHERE indid in(
   SELECT indid FROM ''+@dbname2+''..sysindexkeys WHERE id = a.id AND colid=a.colid
  ))) then 1 else 0 end,
 類型=b.name,

1: 列轉為行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values(''張三'',''語文'',80)
insert test values(''張三'',''數學'',86)
insert test values(''張三'',''英語'',75)
insert test values(''李四'',''語文'',78)
insert test values(''李四'',''數學'',85)
insert test values(''李四'',''英語'',78)

想變成

姓名   語文   數學   英語
張三   80     86     75
李四   78     85     78

declare @sql varchar(8000)
set @sql = ''select name''
select @sql = @sql + '',sum(case km when ''''''+km+'''''' then cj end) [''+km+'']''
 from (select distinct km from test) as a
select @sql = @sql+'' from test group by name''
exec(@sql)

drop table test

eg2:
有表A,
 id pid
 1   1
 1   2
 1   3
 2   1
 2   2
 3   1
如何化成表B:
 id pid
  1  1,2,3
  2  1,2
  3  1
或者是從表B變成A(不要用游標)
以前有相似的列子,現在找不到了,幫幫忙!

--1.創建一個合並的函數
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''''
select @str=@str+'',''+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--調用自定義函數得到結果
select distinct id,dbo.fmerg(id) from 表A

2:
/***********     行轉列   *****************/
測試：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)

declare @ varchar(8000)
set @=''''
select @=@+rtrim(name)+'' from t1 union all select '' from syscolumns where id=object_id(''t1'')
set @=left(@,

a          
-----------
15
9
1
0
1
2
2
0
====================================================================================================
 動態SQL語句     選擇自 txlicenhe 的 Blog 
關鍵字   動態SQL EXEC SP_EXECUTESQL
出處   
 
 1:
普通SQL語句可以用Exec執行
eg:   Select * from tableName
      Exec(''select * from tableName'')
      sp_executesql N''select * from tableName''    -- 請注意字符串前一定要加N

2:
字段名，表名，數據庫名之類作為變量時，必須用動態SQL
eg:  
declare @fname varchar(20)
set @fname = ''[name]''
Select @fname from sysobjects                     -- 錯誤
Exec(''select '' + @fname + '' from sysobjects'')     -- 請注意

加號前後的 單引號的邊上要加空格
exec sp_executesql N'' select '' + @fname + '' from sysobjects''
當然將字符串改成變量的形式也可
    declare @s varchar(1000)
    set @s = ''select '' + @fname + '' from sysobjects''
    Exec(@s)                -- 成功
    exec sp_executesql @s   -- 此句會報錯

    declare @s Nvarchar(1000)  -- 注意此處改為nvarchar(1000)
    set @s = ''select '' + @fname + '' from sysobjects''
    Exec(@s)                -- 成功   
    exec sp_executesql @s   -- 此句正確，

3: 輸出參數
eg:
declare @num,
        @sqls
set @sqls=''select count(*) from  '' + @servername + ''.a.dbo.b''
exec(@sqls)
我如何能將exec執行的結果存入變量@num中

declare @num int,
        @sqls nvarchar(4000)
set @sqls=

介紹取一表前N筆記錄的各種數據庫的寫法...

作者﹕CCBZZP

1. Oracle
SELECT * FROM TABLE1 WHERE ROWNUM<=N
2. INFORMIX
SELECT FIRST N * FROM TABLE1
3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
或者
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
4. SQL Server
SELECT TOP N * FROM TABLE1
5. Sybase
SET ROWCOUNT N
GO
SELECT * FROM TABLE1
6. MySQL
SELECT * FROM TABLE1 LIMIT N
7. FOXPRO
SELECT * TOP N FROM TABLE ORDER BY COLUMN
===================================================================================

create procedure SP_GET_TABLE_INFO
@ObjName varchar(128)       /* The table to generate sql script */
as

declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID   TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length   TinyInt
declare @Prec     TinyInt
declare @Scale    TinyInt
declare @Status   TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID     SmallInt 
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @DBName    varchar(30)
declare @strPri_Key varchar (255)

/*
**  Check to see the the table exists and initialize @objid.
*/
if not Exists(Select name from sysobjects where name = @ObjName)
begin
  select @DBName = db_name()
    raiserror(15009,-1,-1,@ObjName,@DBName)
    return (1)
end

create table #spscript
(
    id     int IDENTITY not null,
    Script Varchar(255) NOT NULL,
    LastLine tinyint
)

declare Cursor_Column INSENSITIVE CURSOR
  for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
        case a.cdefault when 0 then '' '' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key
        from syscolumns a, systypes b where object_name(a.id) = @ObjName
        and a.usertype = b.usertype order by a.ColID

set nocount on
Select @Script = ''Create table '' + @ObjName + ''(''
Insert into #spscript values(@Script,0)

/* Get column information */
open Cursor_Column

fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,@Const_Key

Select @Script = ''''
while (@@FETCH_STATUS <> -1)
begin
  if (@@FETCH_STATUS <> -2)
  begin
    Select @Script = @ColName + '' '' + @TypeName
    if @UserType in (1,2,3,4)
      Select @Script = @Script + ''('' + Convert(char(3),@Length) + '') ''
    else if @UserType in (24)
      Select @Script = @Script + ''('' + Convert(char(3),@Prec) + '',''
                      + Convert(char(3),@Scale) + '') ''
    else
      Select @Script = @Script + '' ''
    if ( @Status & 0x80 ) > 0
      Select @Script = @Script + '' IDENTITY(1,1) ''

    if ( @Status & 0x08 ) > 0
      Select @Script = @Script + '' NULL ''
    else
      Select @Script = @Script + '' NOT NULL ''
    if @cDefault > 0
      Select @Script = @Script + '' DEFAULT '' + @Const_Key
  end
  fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,

/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR
  for Select name,IndID,status from sysindexes where object_name(id)=@ObjName
              and IndID > 0 and IndID<>255  order by IndID   /*增加了對InDid為255的判斷*/
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
while (@@FETCH_STATUS <> -1)
begin
  if @@FETCH_STATUS <> -2
  begin

    declare @i TinyInt
    declare @thiskey varchar(50)
    declare @IndDesc varchar(68) /* string to build up index desc in */

    Select  @i = 1
    while (@i <= 16)
    begin
      select @thiskey = index_col(@ObjName, @IndID, @i)
      if @thiskey is null
        break

      if @i = 1
        select @Index_Key = index_col(@ObjName, @IndID, @i)
      else
        select @Index_Key = @Index_Key + '', '' + index_col(@ObjName, @IndID, @i)
      select @i = @i + 1
    end
    if (@IndStatus & 0x02) > 0
      Select @Script = ''Create unique ''
    else
      Select @Script = ''Create ''
    if @IndID = 1
      select @Script = @Script + '' clustered ''

    if (@IndStatus & 0x800) > 0
&nbsp;    select @strPri_Key = '' PRIMARY KEY ('' + @Index_Key + '')''
    else
     select @strPri_Key = ''''
    
    if @IndID > 1
      select @Script = @Script + '' nonclustered ''
    Select @Script = @Script + '' index '' + @ColName + '' ON ''+ @ObjName
           + ''('' + @Index_Key + '')''
    Select @IndDesc = ''''
    /*
 **  See if the index is ignore_dupkey (0x01).
    */
    if @IndStatus & 0x01 = 0x01
      Select @IndDesc = @IndDesc + '' IGNORE_DUP_KEY'' + '',''
    /*
     **  See if the index is ignore_dup_row (0x04).
    */
   /* if @IndStatus & 0x04 = 0x04 */
   /*   Select @IndDesc = @IndDesc + '' IGNORE_DUP_ROW'' + '','' */ /* 2000 不在支持*/
    /*
 **  See if the index is allow_dup_row (0x40).
    */
    if @IndStatus & 0x40 = 0x40
      Select @IndDesc = @IndDesc + '' ALLOW_DUP_ROW'' + '',''
    if @IndDesc <> ''''
    begin
      Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
      Select @Script = @Script + '' WITH '' + @IndDesc
    end
    /*
 **  Add the location of the data.
    */
  end
  if (@strPri_Key = '''')
    Insert into #spscript values(@Script,0)
  else
    update #spscript set Script = Script + @strPri_Key where LastLine = 1
 
  Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
end
Close Cursor_Index
Deallocate Cursor_Index

Select Script from #spscript

set nocount off

return (0)

===================================================

-- ======================================================

--列出SQL Server 所有表，字段名，主鍵，類型，長度，小數位數等信息

--在查詢分析器裡運行即可,可以生成一個表，導出到Excel中

-- ======================================================

SELECT

       (case when a.colorder=1 then d.name else '''' end)表名,

       a.colorder 字段序號,

       a.name 字段名,

       (case when COLUMNPROPERTY( a.id,a.name,''IsIdentity'')=1 then ''√''else '''' end) 標識,

       (case when (SELECT count(*)

       FROM sysobjects

       WHERE (name in

                 (SELECT name

                FROM sysindexes

                WHERE (id = a.id) AND (indid in

                          (SELECT indid

                         FROM sysindexkeys

                         WHERE (id = a.id) AND (colid in

                                   (SELECT colid

                                  FROM syscolumns

                   &nbsp;              WHERE (id = a.id) AND (name = a.name))))))) AND

              (xtype = ''PK''))>0 then ''√'' else '''' end) 主鍵,

       b.name 類型,

       a.length 占用字節數,

       COLUMNPROPERTY(a.id,a.name,''PRECISION'') as 長度,

       isnull(COLUMNPROPERTY(a.id,a.name,''Scale''),0) as 小數位數,

       (case when a.isnullable=1 then ''√''else '''' end) 允許空,

       isnull(e.text,'''') 默認值,

       isnull(g.[value],'''') AS 字段說明   

FROM  syscolumns  a left join systypes b

on  a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id  and  d.xtype=''U'' and  d.name<>''dtpropertIEs''

left join syscomments e

on a.cdefault=e.id

left join syspropertIEs g

on a.id=g.id AND a.colid = g.smallid 

order by a.id,a.colorder

-------------------------------------------------------------------------------------------------

列出SQL Server 所有表、字段定義，類型，長度，一個值等信息

並導出到Excel 中

-- ======================================================

-- Export all user tables definition and one sample value

-- jan-13-2003,Dr.Zhang

-- ======================================================

在查詢分析器裡運行：

SET ANSI_NULLS OFF

GO

SET NOCOUNT ON

GO

SET LANGUAGE ''SimplifIEd Chinese''

go

DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)

SELECT d.name TableName,a.name FIEldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t

FROM  syscolumns  a,  systypes b,sysobjects d 

WHERE  a.xtype=b.xusertype  and  a.id=d.id  and  d.xtype=''U''

DECLARE read_cursor CURSOR

FOR SELECT TableName,FIEldName FROM #t

SELECT TOP 1 ''_TableName                     '' TableName,

            ''FieldName                      '' FIEldName,''TypeName             '' TypeName,

            ''Length'' Length,''IS_NULL'' IS_NULL,

            ''MaxLenUsed'' AS MaxLenUsed,''Sample Value          '' Sample,

;         ''Comment   '' Comment INTO #tc FROM #t

OPEN read_cursor

FETCH NEXT FROM read_cursor INTO @tbl,@fld

WHILE (@@fetch_status <> -1)  --- failes

BEGIN

       IF (@@fetch_status <> -2) -- Missing

       BEGIN

              SET @sql=N''SET @maxlen=(SELECT max(len(cast(''+@fld+'' as nvarchar))) FROM ''+@tbl+'')''

              --PRINT @sql

              EXEC SP_EXECUTESQL @sql,N''@maxlen int OUTPUT'',@maxlen OUTPUT

              --print @maxlen

              SET @sql=N''SET @sample=(SELECT TOP 1 cast(''+@fld+'' as nvarchar) FROM ''+@tbl+'' WHERE len(cast(''+@fld+'' as

nvarchar))=''+convert(nvarchar(5),@maxlen)+'')''

              EXEC SP_EXECUTESQL @sql,

              --for quickly  

              --SET @sql=N''SET @sample=convert(varchar(20),(SELECT TOP 1 ''+@fld+'' FROM ''+

                     --@tbl+'' order by 1 desc ))'' 

              PRINT @sql

              print @sample

              print @tbl

              EXEC SP_EXECUTESQL @sql,N''@sample nvarchar(30) OUTPUT'',@sample OUTPUT

              INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,

                     convert(nchar(20),ltrim(ISNULL(@sample,'' ''))) as Sample,'' '' Comment FROM #t where TableName=@tbl and

FIEldName=@fld

       END

       FETCH NEXT FROM read_cursor INTO @tbl,@fld

END

CLOSE read_cursor

DEALLOCATE read_cursor

GO

SET ANSI_NULLS ON

GO

SET NOCOUNT OFF

GO

select count(*)  from #t

DROP TABLE #t

GO

select count(*)-1  from #tc

select * into ##tx from #tc order by tablename

DROP TABLE #tc

--select * from ##tx

declare @db nvarchar(60),@sql nvarchar(3000)

set @db=db_name()

--請修改用戶名和口令 導出到Excel 中

set @sql=''exec master.dbo.xp_cmdshell ''''bcp ..dbo.##tx out c:\''+@db+''_exp.xls -w -C936 -Usa -Psa ''''''

print @sql

exec(@sql)

GO

DROP TABLE ##tx

GO

-- ======================================================

--根據表中數據生成insert語句的存儲過程

--建立存儲過程，執行 spGenInsertSQL 表名

--感謝playyuer

-- ======================================================

CREATE   proc spGenInsertSQL (@tablename varchar(256))

as

begin

  declare @sql varchar(8000)

  declare @sqlValues varchar(8000)

  set @sql ='' (''

  set @sqlValues = ''values (''''+''

  select @sqlValues = @sqlValues + cols + '' + '''','''' + '' ,@sql = @sql + ''['' + name + ''],''

    from

        (select case

                  when xtype in (48,52,56,59,60,62,104,106,108,122,127)                               

                       then ''case when ''+ name +'' is null then ''''NULL'''' else '' + ''cast(''+ name + '' as varchar)''+'' end''

                  when xtype in (58,61)

                       then ''case when ''+ name +'' is null then ''''NULL'''' else ''+'''''''''''''''''' + '' + ''cast(''+ name +'' as varchar)''+

''+''''''''''''''''''+'' end''

                 when xtype in (167)         then ''case when ''+ name +'' is null then ''''NULL'''' else ''+'''''''''''''''''' + '' + ''replace(''+

name+'','''''''''''''''','''''''''''''''''''''''')'' + ''+''''''''''''''''''+'' end''

                       then ''case when ''+ name +'' is null then ''''NULL'''' else ''+''''''N'''''''''''' + '' + ''replace(''+

name+'','''''''''''''''','''''''''''''''''''''''')'' + ''+''''''''''''''''''+'' end''

                  when xtype in (175)

                       then ''case when ''+ name +'' is null then ''''NULL'''' else ''+'''''''''''''''''' + '' + ''cast(replace(''+

name+'','''''''''''''''','''''''''''''''''''''''') as Char('' + cast(length as varchar)  + ''))+''''''''''''''''''+'' end''

                  when xtype in (239)

                       then ''case when ''+ name +'' is null then ''''NULL'''' else ''+''''''N'''''''''''' + '' + ''cast(replace(''+

name+'','''''''''''''''','''''''''''''''''''''''') as Char('' + cast(length as varchar)  + ''))+''''''''''''''''''+'' end''

                  else ''''''NULL''''''

                end as Cols,name

           from syscolumns 

          where id = object_id(@tablename)

        ) T

  set @sql =''select ''''INSERT INTO [''+ @tablename + '']'' + left(@sql,len(@sql)-1)+'') '' + left(@sqlValues,len(@sqlValues)-4) +

'')'''' from ''+@tablename

  --print @sql

  exec (@sql)

end

GO

-- ======================================================

--根據表中數據生成insert語句的存儲過程

--建立存儲過程，執行 proc_insert 表名

--感謝Sky_blue

-- ======================================================

CREATE proc proc_insert (@tablename varchar(256))

as

begin

       set nocount on

       declare @sqlstr varchar(4000)

       declare @sqlstr1 varchar(4000)

       declare @sqlstr2 varchar(4000)

       select @sqlstr=''select ''''insert ''+@tablename

       select @sqlstr1=''''

       select @sqlstr2='' (''

       select @sqlstr1= '' values ( ''''+''

       select @sqlstr1=@sqlstr1+col+''+'''',''''+'' ,@sqlstr2=@sqlstr2+name +'','' from (select case

--     when a.xtype =173 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(''+convert(varchar

(4),a.length*2+2)+''),''+a.name +'')''+'' end''

       when a.xtype =104 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(1),''+a.name +'')''+'' end''

       when a.xtype =175 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''''''''''''''''''+''+''replace

(''+a.name+'','''''''''''''''','''''''''''''''''''''''')'' + ''+''''''''''''''''''+'' end''

       when a.xtype =61  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''''''''''''''''''+''+''convert(varchar(23),''+a.name

+'',121)''+ ''+''''''''''''''''''+'' end''

       when a.xtype =106 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(''+convert(varchar

(4),a.xprec+2)+''),''+a.name +'')''+'' end''

       when a.xtype =62  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(23),''+a.name +'',2)''+'' end''

       when a.xtype =56  then ''case when ''+

       when a.xtype =60  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(22),''+a.name +'')''+'' end''

       when a.xtype =239 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''''''''''''''''''+''+''replace

(''+a.name+'','''''''''''''''','''''''''''''''''''''''')'' + ''+''''''''''''''''''+'' end''

       when a.xtype =108 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(''+convert(varchar

(4),a.xprec+2)+''),''+a.name +'')''+'' end''

       when a.xtype =231 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''''''''''''''''''+''+''replace

(''+a.name+'','''''''''''''''','''''''''''''''''''''''')'' + ''+''''''''''''''''''+'' end''

       when a.xtype =59  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(23),''+a.name +'',2)''+'' end''

       when a.xtype =58  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''''''''''''''''''+''+''convert(varchar(23),''+a.name

+'',121)''+ ''+''''''''''''''''''+'' end''

       when a.xtype =52  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(12),''+a.name +'')''+'' end''

       when a.xtype =122 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(22),''+a.name +'')''+'' end''

       when a.xtype =48  then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(6),''+a.name +'')''+'' end''

--     when a.xtype =165 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''convert(varchar(''+convert(varchar

(4),a.length*2+2)+''),''+a.name +'')''+'' end''

       when a.xtype =167 then ''case when ''+a.name+'' is null then ''''NULL'''' else ''+''''''''''''''''''+''+''replace

(''+a.name+'','''''''''''''''','''''''''''''''''''''''')'' + ''+''''''''''''''''''+'' end''

       else ''''''NULL''''''

       end as col,a.colid,a.name

       from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and  a.xtype

<>36

       )t order by colid

       select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+'') ''+left(@sqlstr1,len(@sqlstr1)-3)+'')'''' from ''+@tablename

--  print @sqlstr

       exec( @sqlstr)

       set nocount off

end

GO

說明：本貼純屬收藏，目的在於大家交流，在此對作者表示感謝！

==========================================================================================
小寫轉大寫金額     選擇自 webmin 的 Blog 
關鍵字   金額 SQL Server
出處   
 
 在網上見到一個Oracle的版本的小寫轉大寫金額的函數，感覺還不錯現在把它轉成SQL Server版本。

/********************************************************
作者：([email protected])
版本：1.0
創建時間：20020227
修改時間：
功能：小寫金額轉換成大寫
參數：n_LowerMoney 小寫金額
          v_TransType 種類 -- 1: directly translate, 0: read it in Words
輸出：大寫金額
********************************************************/
CREATE FUNCTION dbo.L2U (@n_LowerMoney numeric(15,2),@v_TransType int) 
RETURNS VARCHAR(200) AS 
BEGIN
Declare @v_LowerStr VARCHAR(200) -- 小寫金額
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大寫金額
Declare @i_I int

set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四捨五入為指定的精度並刪除數據左右空格
set @i_I = 1
set @v_UpperStr = ''''

while ( @i_I <= len(@v_LowerStr))
begin
      select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,

if ( 0 = @v_TransType)
begin
set @v_UpperStr = REPLACE(@v_UpperStr,''零拾'',''零'')
set @v_UpperStr = REPLACE(@v_UpperStr,''零佰'',''零'')
set @v_UpperStr = REPLACE(@v_UpperStr,''零仟'',''零'')
set @v_UpperStr = REPLACE(@v_UpperStr,''零零零'',''零'')
set @v_UpperStr = REPLACE(@v_UpperStr,''零零'',''零'')
set @v_UpperStr = REPLACE(@v_UpperStr,''零角零分'',''整'')
set @v_UpperStr = REPLACE(@v_UpperStr,''零分'',''整'')
set @v_UpperStr = REPLACE(@v_UpperStr,

-- 對壹元以下的金額的處理
if ( ''元'' = substring(@v_UpperStr,1,1))
begin
     set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if ( ''零'' = substring(@v_UpperStr,1,1))
begin
     set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if ( ''角'' = substring(@v_UpperStr,1,1))
begin
     set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if ( ''分'' = substring(@v_UpperStr,1,1))
begin
     set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if (''整'' = substring(@v_UpperStr,1,1))
begin
     set @v_UpperStr = ''零元整''
end
return @v_UpperStr
END

例子：

select dbo.L2U(56588441.111,0)

select dbo.L2U(00.00,0)
================================================================================
 
SQL高手篇:精妙SQL語句介紹

說明：復制表(只復制結構,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1
說明：拷貝表(拷貝數據,源表名：a 目標表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;
說明：顯示文章、提交人和最後回復時間

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
select a.SysID,(select count(0) as count from info_out_all where info_out_all.ReID=a.SysID) as b from info_out_all a
說明：外連接查詢(表名1：a 表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
說明：日程安排提前五分鐘提醒

SQL:  select * from 日程安排 where datediff(''minute'',f開始時間,getdate())>5

說明：兩張關聯表，刪除主表中已經在副表中沒有的信息
SQL: 

delete from info where not exists ( select * from infobz where info.infid=infobz.infid 
說明：--
SQL: 

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
  FROM TABLE1,
    (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
        FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
                FROM TABLE2
              WHERE TO_CHAR(UPD_DATE,''YYYY/MM'') = TO_CHAR(SYSDATE, ''YYYY/MM'')) X,
            (SELECT NUM, UPD_DATE, STOCK_ONHAND
                FROM TABLE2
              WHERE TO_CHAR(UPD_DATE,''YYYY/MM'') =
                    TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ''YYYY/MM'') || ''/01'',''YYYY/MM/DD'') - 1, ''YYYY/MM'')  Y,
        WHERE X.NUM = Y.NUM （+）
          AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND  B
WHERE A.NUM = B.NUM
說明：--
SQL: 

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱

=''"&strdepartmentname&"'' and 專業名稱=''"&strprofessionname&"'' order by 性別,生源地,高考總成績
說明：
從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源）
SQL:

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ''yyyy'') AS telyear,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''01'', a.factration)) AS JAN,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''02'', a.factration)) AS FRI,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''03'', a.factration)) AS MAR,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''04'', a.factration)) AS APR,SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''05'', a.factration)) AS MAY,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''06'', a.factration)) AS JUE,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''07'', a.factration)) AS JUL,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''08'', a.factration)) AS AGU,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''09'', a.factration)) AS SEP,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''10'', a.factration)) AS OCT,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''11'', a.factration)) AS NOV,
      SUM(decode(TO_CHAR(a.telfeedate, ''mm''), ''12'', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
        FROM TELFEESTAND a, TELFEE b
        WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ''yyyy'')
說明：四表聯查問題：

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c  inner join d on a.a=d.d where .....
說明：得到表中最小的未使用的ID號
SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
 FROM  Handle
 WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

查詢10到20條記錄

select top 10 * from (select top 20 * from employees order by employeeid) a order by employeeid desc

CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT ''001'',NULL ,''山東省''
UNION ALL SELECT ''002'',''001'',''煙台市''
UNION ALL SELECT ''004'',''002'',''招遠市''
UNION ALL SELECT ''003'',''001'',''青島市''
UNION ALL SELECT ''005'',NULL ,''四會市''
UNION ALL SELECT ''006'',''005'',''清遠市''
UNION ALL SELECT ''007'',''006'',''小分市''
GO

--查詢指定節點及其所有子節點的函數
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,

--調用函數查詢002及其所有子節點
SELECT a.*
FROM tb a,f_Cid(''002'') b
WHERE a.ID=b.ID
/*--結果
ID PID Name
------ ------- ----------
002 001 煙台市
004 002 招遠市
--*/

CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
 DECLARE @Level int
 SET @Level=1
 INSERT @t_Level SELECT @ID,@Level
 WHILE @@ROWCOUNT>0
 BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.PID,@Level
  FROM tb a,@t_Level b
  WHERE a.ID=b.ID
   AND b.Level=@Level-1
 END
 RETURN
END
GO

--上面的用戶定義函數可以處理一個節點有多個父節點的情況，對於標准的樹形數據而言，由於每個節點僅有一個父節點，所以也可以通過下

面的用戶定義函數實現查找標准樹形數據的父節點。
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
 INSERT @t_Level SELECT @ID
 SELECT @ID=PID FROM tb
 WHERE ID=@ID
  AND PID IS NOT NULL
 WHILE @@ROWCOUNT>0
 BEGIN
  INSERT @t_Level SELECT @ID
  SELECT @ID=PID FROM tb
  WHERE ID=@ID
   AND PID IS NOT NULL
 END
 RETURN
END

CREATE FUNCTION dbo.f_ChangeCodeRule(
@Old_CodeRule  varchar(50),  --以逗號分隔的舊的編碼規則,每層編碼的長度,比如1,2,3,表示有三層編碼,第一層長度為1,第二層長度為2,

第三層長度為3
@New_CodeRule varchar(50),   --以逗號分隔的舊的編碼規則,如果某個層次的編碼長度為0,表示刪除該層編碼
@CharFill       char(1),     --擴充編碼時,填充的字符
@Position       int,         --為0,從編碼的最前面開始壓縮或者填充,為-1或者大於舊編碼的長度,從最後一位開始處理,為其他值,從指定

的位置後開始處理
@FIEldName     sysname       --編碼字段名
)RETURNS nvarchar(4000)
AS文章整理:學網 http://www.xue5.com (本站) [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21] [22] [23] [24] [25] [26] [27] [28] [29] [30] [31] [32] [33] [34] [35] [36] [37] [38] [39]

r />BEGIN
 IF ISNULL(@CharFill,'''')='''' SET @CharFill=N''0''
 DECLARE @old_Code TABLE(ID int IDENTITY,CodeLen int,CodeLens int,Code nvarchar(200))
 DECLARE @new_Code TABLE(ID int IDENTITY,CodeLen int)
 --插分舊編碼規則到表
 DECLARE @CodeLen varchar(10),@CodeLens varchar(10)
 SET @CodeLens=1
 WHILE CHARINDEX(N'','',@Old_CodeRule)>0
 BEGIN
  SELECT @CodeLen=LEFT(@Old_CodeRule,CHARINDEX(N'','',@Old_CodeRule)-1),
   @Old_CodeRule=STUFF(@Old_CodeRule,1,CHARINDEX(N'','',@Old_CodeRule),N'''')
  INSERT @old_Code VALUES(@CodeLen,@CodeLens,N''SUBSTRING(''+@FIEldName+N'',''+@CodeLens+N'',''+@CodeLen+N'')'')
  SET @CodeLens=@CodeLens+CAST(@CodeLen as int)
 END
 INSERT @old_Code VALUES(@Old_CodeRule,@CodeLens,N''SUBSTRING(''+@FIEldName+N'',''+@CodeLens+N'',''+@Old_CodeRule+N'')'')

 --插分新編碼規則到表
 WHILE CHARINDEX(N'','',@New_CodeRule)>0
 BEGIN
  INSERT @new_Code VALUES(LEFT(@New_CodeRule,CHARINDEX(N'','',@New_CodeRule)-1))
  SET @New_CodeRule=STUFF(@New_CodeRule,1,CHARINDEX(N'','',@New_CodeRule),N'''')
 END
 INSERT @new_Code VALUES(@New_CodeRule)

 --生成編號規則修改處理語句
 DECLARE @sql nvarchar(4000)
 SET @sql=''''
 SELECT @sql=@sql
  +CASE
   WHEN n.CodeLen=0 THEN ''''                   --新編碼長度為0,表示去掉這段編碼
   ELSE N''+CASE WHEN LEN(''+@FIEldName
    +N'')<''+CAST(o.CodeLens as varchar)
    +N'' THEN '''''''' ELSE ''+CASE
     WHEN n.CodeLen=o.CodeLen THEN N''+''+o.Code  --新舊編碼長度相同時不需要處理
     WHEN n.CodeLen>o.CodeLen THEN CASE         --擴充編碼長度的處理,根據@Position和舊編碼

長度決定編碼的填充位置
     &nbs歡迎光臨學網,收藏本篇文章 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21] [22] [23] [24] [25] [26] [27] [28] [29] [30] [31] [32] [33] [34] [35] [36] [37] [38] [39]

p;WHEN @Position=-1 OR @Position>=o.CodeLen
      THEN N''+''+o.Code
       +N''+''+QUOTENAME(REPLICATE(@CharFill,n.CodeLen-o.CodeLen),N'''''''')
      ELSE N''+STUFF(''+o.Code
       +N'',''+CAST(@Position+1 as varchar)
       +N'',0,''+QUOTENAME(REPLICATE(@CharFill,n.CodeLen-o.CodeLen),N'''''''')
       +N'')''
      END
     ELSE CASE                                  --收縮編碼長度的處理,根據@Position和新編碼

長度決定編碼的截取位置
      WHEN @Position=-1 OR @Position>n.CodeLen
      THEN ''+LEFT(''+o.Code+N'',''+CAST(n.CodeLen as varchar)+N'')''
      ELSE N''+STUFF(''+o.Code
       +N'',''+CAST(@Position+1 as varchar)
       +N'',''+CAST(o.CodeLen-n.CodeLen as varchar)
       +N'','''''''')''
      END
     END
    +N'' END''
   END
 FROM @old_Code o,@new_Code n
 WHERE o.ID=n.ID
 RETURN(STUFF(@sql,1,1,N''''))
END