sql 自定义函数--十进制转二进制

随笔记忆:
 1 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DECTOBIN]') and xtype in (N'FN', N'IF', N'TF'))
 2 drop function [dbo].[DECTOBIN]
 3 GO
 4 
 5 SET QUOTED_IDENTIFIER ON 
 6 GO
 7 SET ANSI_NULLS ON 
 8 GO
 9 
10 create FUNCTION dbo.DECTOBIN(@Dec INT, @StrLen INT)
11 RETURNS VARCHAR(31) 
12 AS
13 BEGIN
14     DECLARE @BinStr AS VARCHAR(31)      
15     DECLARE @Div2 AS INT                
16     DECLARE @Mod2 AS INT                
17     IF @Dec < 0
18         RETURN 'NULL'              
19     SET @Div2 = @Dec / 2 
20     SET @Mod2 = @Dec % 2 
21     SET @BinStr = ''
22     WHILE @Div2 <> 0
23     BEGIN
24         SET @BinStr = CAST(@Mod2 AS CHAR(1)) + @BinStr
25         SET @Dec = @Dec / 2
26         SET @Div2 = @Dec / 2
27         SET @Mod2 = @Dec % 2
28    
29     END;
30     SET @BinStr = CAST(@Mod2 AS CHAR(1)) + @BinStr 
31     IF @StrLen > LEN(@BinStr)    
32         BEGIN
33             IF @StrLen > 31    
34                 SET @StrLen = 31
35    
36             DECLARE @ZeroStr VARCHAR(31)   
37             DECLARE @OffsetLen TINYINT       
38             SET @ZeroStr = ''
39             SET @OffsetLen = @StrLen - LEN(@BinStr)
40    
41             WHILE  @OffsetLen > 0
42             BEGIN
43                 SET @ZeroStr = @ZeroStr + '0'
44                 SET @OffsetLen = @OffsetLen - 1
45             END
46    
47             SET @BinStr = @ZeroStr + @BinStr
48         END
49     RETURN @BinStr
50 
51 END
52 
53 
54 GO
55 SET QUOTED_IDENTIFIER OFF 
56 GO
57 SET ANSI_NULLS ON 
58 GO