SQL SERVER开发技巧

批量复制实用工具

发布日期：2001年9月4日

问：我如何从SQL Server中创建一个纯文本的平面文件作为另一个应用程序的输入？

答： 扩展标记语言（XML）的目的之一就是解决类似这样的难题，但是在所有程序都支持XML之前，你可以考虑使用我们对之充满信心的替代手段，就是批量复制程序（bcp）工具。该工具不仅可以用来转储一个数据表；bcp还可以从一个视图而不是从一个表中获得输入。在您指定了作为输入源的视图之后，你可以通过选择适当的过滤子句（WHERE和HAVING）将输出限制为列的子集或者行的子集。

更重要的是，通过使用视图，你可以从多个联合表中导出数据。你唯一不能做的事情就是指定被写入平面文件中的行的顺序，因为视图中不会包括ORDER BY子句，除非你还使用了TOP关键词。

如果你想以特别的顺序生成数据，或者如果你不能预测出所要导出数据的内容，你应该知道，除了视图之外，bcp还支持使用实际的查询。使用查询代替视图或者表所要注意的唯一“须知”是：你必须在bcp命令行的out位置指定queryout。

例如，你可以通过以下代码使用bcp从pubs数据库中生成居住在California的作者列表：

bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout c:\CAauthors.txt -c -T -S

— SQL Server MVPs

随机对查询结果进行排序

发布日期：2001年8月20日

问：如何对查询结果随机排序？

答： 为了对行进行随机排序，或者返回随机选择的X行数据，你可以在SELECT语句中使用RAND函数。但是RAND函数在整个查询中只被计算一次，所以所有的行都具有相同的值。你可以使用ORDER BY子句根据从NEWID函数返回的结果来排序行，如以下代码所示：

SELECT *
FROM Northwind..Orders
ORDER BY NEWID()

SELECT TOP 10 *
FROM Northwind..Orders
ORDER BY NEWID()

— SQL Server MVPs

在视图中使用ORDER BY子句

发布日期：2001年8月27日

问：为什么SQL Server不允许在视图定义使用ORDER BY子句？

答： SQL Server之所以不允许在视图定义中使用ORDER BY子句是为了遵守ANSI SQL-92标准。因为对该标准的原理分析需要对结构化查询语言（SQL）的底层结构和它所基于的数学理论进行讨论，我们不能在这里对它进行充分的解释。但是，如果你需要在视图中指定ORDER BY子句，可以考虑使用以下方法：

USE pubs
GO

CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO

Microsoft在SQL Server 7.0中引入的TOP结构在同ORDER BY子句结合使用时是非常有用的。只有在同TOP关键词结合使用时，SQL Server才支持在视图中使用ORDER BY子句。

注意：TOP关键词是SQL Server对ANSI SQL-92标准的扩展。

— SQL Server MVPs

将Access数据转换为XML格式

发布日期：2002年4月4日

问：我怎样才能将Microsoft Access数据表中的数据转换为XML格式？

答：以下应用程序可以帮助您将Access数据转换为XML格式：Access 2002、 ADO 2.5和SQLXML。您可以通过Access 2002（Microsoft Office XP的一部分）查询数据或者使用XML格式保存数据。您可能想自动完成这个转换过程。ADO 2.5及其后续版本使您可以将数据打开到一个记录集中，然后以XML格式持有记录集，如以下代码所示：

rs.Save "c:\rs.xml", adPersistXML

您还可以使用链接服务器将Access 数据库添加到SQL Server 2000数据库，以便从SQL Server内部运行查询和检索数据。最后，您可以通过HTTP，使用SQLXML技术以XML格式抽取出所需的Access数据。

确保所有非空值的唯一性

发布日期：2002年3月11日

问：我的SQL Server数据表中有一列允许数据为空值（NULL）。我希望当该列中的数据为非空值时，这些数据都是唯一的。通过编程实现这一点的最好办法是什么？如果我可以在该列上放置一个UNIQUE约束，我只能在该列包含一条具有空值的记录。我正在使用一个触发器强化这种限制，但是您能为我推荐一种更为简单的方法确保所有非空值均是唯一的吗？

答： SQL Server没有一种内置的机制来禁止空值以外的值发生重复，所以你需要自行设定一个CHECK约束实现这种限制。例如，以下代码就可以实现你的目的。

USE tempdb
CREATE table t1 (c1 int NULL, c2 char(5) NULL)
CREATE trigger mytrigger on t1 for insert, update as
BEGIN
       IF (select max(cnt) from (select count(i.c1)
as cnt from t1, inserted i where t1.c1=i.c1 group
by i.c1) x) > 1
       ROLLBACK TRAN
END

在SQL Server 2000中，您还可以使用INSTEAD OF触发器来执行这种唯一性限制。同INSTEAD OF触发器有关的更多详细信息，请参阅以下文章。为了找到这些文章，您可以访问SQL Server Magazine站点，并在InstantDoc框中输入这些文章的InstantDoc（快速文档）编号，然后点击“Go”。这些文章是：

§                        INSTEAD OF触发器的几点使用窍门；InstantDoc编号：15828

§                        视图中的INSTEAD OF触发器；InstantDoc编号：15791

§                        INSTEAD OF触发器；InstantDoc编号：15524

— SQL Server MVPs

测试从Microsoft Visual Basic 6.0到 SQL Server的连接

发布日期：2001年11月5日

问：我想测试一个从Microsoft Visual Basic V 6.0到SQL Server的连接。我开始想通过SQL-DMO 的VerifyConnection属性进行测试，但是没有取得成功。列表1列出了我所使用的代码。这些代码有什么错误吗？

列表1：读者的代码，通过SQL-DMO的VerifyConnection属性对SQL Server连接进行测试的尝试没有取得成功

Public oSQLServer As New SQLDMO.SQLServer
oSQLServer.LoginTimeout = 10
oSQLServer.Connect StrServer, strUser, StrPwd
oSQLServer.StatusInfoRefetchInterval
(SQLDMOStatInfo_AutoVerifyConnection) = 10
    CnOk = oSQLServer.IsLogin("sa")
If Err.Number <> 0 Then
    oSQLServer.ReConnect
End If
If oSQLServer.VerifyConnection(2) Then
       'ok
Else
       'not ok
End if

答： 如果你需要了解从Visual Basic到SQL Server的连接是否能够正常工作，你可以使用PingSQLServerVersion 方法，如下面的列表2所示。同你所写的脚本相比，这些代码的运行速度很快而且耗费的资源也很少。此外，VerifyConnection 仅仅能验证已经建立的连接。在你对VerifyConnection的调用中，参数应该是 boolean 而不是int。还有，您的如下代码：

oSQLServer.StatusInfoRefetchInterval
(SQLDMOStatInfo_AutoVerifyConnection) = 10

会降低系统性能，因为你在断定连接状态时使用了10秒钟的间隔周期而不是30秒。

列表2：通过SQL-DMO的PingSQLServerVersion方法测试SQL Server连接的代码。

On Error Resume Next

Dim oSQLServer As SQLDMO.SQLServer
Dim sqlversion As SQLDMO_SQL_VER
Set oSQLServer = New SQLDMO.SQLServer
sqlversion = oSQLServer.PingSQLServerVersion("SERVERNAME")
     If Err.Number <> 0 Or sqlversion = SQLDMOSQLVer_Unknown Then
     ' You have a problem.
End If
Set oSQLServer = Nothing

— Microsoft SQL Server开发团队

截断导出到Excel的数据

发布日期：2002年2月18日

问：我开发了一个数据转换服务（Data Transformation Services，DTS）包，用来将数据从SQL Server 7.0数据表中导出到一个Microsoft Excel电子表格中。该转换包第一次运行时工作得非常好，但是在后来的运行过程中，它并不替换电子表格中的数据，而是将数据添加到电子表格中。我怎样解决这个问题？

答： 听起来，您需要执行一个TRUNCATE 语句，然后创建一个新的插入。你您可以通过两种方法完成这些步骤：使用Excel中的自动化模型清空电子表格，或者使用类似列表1那样的一个小脚本在所填充电子表格的上面添加一个空白的Excel电子表格。

列表1：示例脚本，用来在所填充电子表格的上面再添加一个空白的Excel电子表格

Dim sDBFileName
Dim sTemplateFile
Dim oFSO

sDBFileName = "d:\data\acc_demo.xls"
sTemplateFile = "d:\data\acc_temp.xls"

SET oFSO = CreateObject("Scripting.FileSystemObject")

'If the file exists, delete it.
IF(oFSO.FileExists( sDBFileName )) THEN
oFSO.DeleteFile( sDBFileName )
END IF

'Optionally, copy from another file.
oFSO.CopyFile sTemplateFile, sDBFileName

SET oFSO = Nothing

— SQL Server 开发团队

Yes/No字符串

问题：问题：我在程序中使用一个字符串，该字符串包含对一系列问题所做出的是与否（Y/N）形式的回答。请问如何才能快速统计出该字符串中两种答案的数量分别是多少？

解答：对于SQL Server 7.0或者更新版本，您可以提交以下形式的Transact-SQL代码：

DECLARE @TestString varchar (12)

SET @TestString = 'YNYYNNYYYNNN'

SELECT NumY = LEN (REPLACE (@TestString, 'N', '')),

NumN = LEN (REPLACE (@TestString, 'Y', ''))

尽管SQL Server并未支持众多内建字符串操作函数，然而，只要稍做变通，您便可以通过组合使用现有函数的方式来实现这些功能。

ANSI JOIN 与以前的 OUTER JOIN 语法

2001年7月23日

 

 


问： 是否可以使用 ANSI 式联接而不使用老式联接提高性能？

答： 转换到使用 ANSI 式 JOIN 语法不会获得任何性能上的提高。但是，在处理外围联接时，老式的 JOIN (=* or *=) 与 ANSI 式 JOIN 不完全对等。

我们强烈建议您转移到 ANSI 式 JOIN 语法。虽然老的习惯很难改变（我们直到，我们同样经历过），我们发现在我们习惯了 ANSI 式 JOIN 后，我们将很快习惯将它用于代码查询。使用 ANSI-JOIN 语法将让您可以具有很重要的优势：因为联接逻辑是与过滤标准清晰分隔的，所以您可以更快的了解查询逻辑。

应该意识到我们不应该期待能够自动地将老式的 OUTER JOIN 转换为 ANSI 式 JOIN 并接收到相同的输出。不能复制结果，因为 SQL Server 老式 JOIN 在执行联接前执行过滤条件，但是 ANSI 式 JOIN 逆反了该过程（联接逻辑在过滤之前）。

可能转换到 ANSI 式 JOIN 的最主要的强制因素是 Microsoft 已经明确表示 SQL Server 将不支持老式 OUTER JOIN 语法。其他的重要考虑是 ANSI 式 JOIN 支持老式的 JOIN 语法不支持的查询结构。

-SQL Server MVP

设置拥有者前缀以避免重新编译

Q. SQL Server Magazine的2001年第6期“Answers from Microsoft”专栏中的一句话引起了我的注意：“Recompilations might be the source of the slower stored procedure speed.(重新编译可能是引起存储过程速度变慢的原因)。”该专栏建议对所有的引用表、视图和过程，编码时加上自己的前缀。 我是否应当将这个步骤加到我的编码标准中以避免出现性能问题？你能否提供关于重新编译的后果的更多信息？

A. 是的，如果你知道前缀，就应总是使用自己的前缀。让我们看一个为什么使用自己的前缀可以改进查询性能的例子。当用户Fred调用存储过程dbo.foo，而dbo.foo运行查询

SELECT col1 FROM table1

时，查询优化器必须决定是检索fred.table1还是检索dbo.table1。然后，当用户Mark调用同一个存储过程时，查询优化器必须对查询计划进行重新编译，以决定用户是需要mark.table1还是需要dbo.table1。

如果你的SELECT语句编码如下

SELECT col1 FROM dbo.table1

查询优化器将不会遇到任何模糊性，从而避免重新编译。

—Microsoft SQL Server开发组

配置单向合并复制

2001年8月13日

 

 


问：如何将 SQL Server 合并复制配置不只在一个方向起作用？

答： 默认情况下，使用合并复制可以在两个方向传递数据：在发布程序中作出的更改将被传送到订阅程序，在订阅程序作出的更改将被传回发布程序。在某些情况下，可能需要使用合并复制，但是允许数据在一个方向流动。SQL Server 不提供实现该操作的接口。

但是，请注意合并代理只是一个可执行文件 (Replmerg.exe)。SQL Server Agent 中的合并工作只是简单地调用这个可执行文件并将命令行参数传递给它。这些可选的命令行参数之一是 ExchangeType，它具有三个可能值：1 指定推入，2 指定取出，3 指定双向（它是默认值）。如果要用推入订阅来设置合并复制，并且还要将 ExchangeType 设置为 1，则 SQL Server 将把数据从发布程序推入到订阅程序，而不是将数据从订阅程序发送回发布程序。

-SQL Server MVP

在计算列中创建 UNIQUE 和 PRIMARY KEY 约束

2001年7月2日

 

 


问：可以在 SQL Server 2000 的计算列中创建 UNIQUE 和 PRIMARY KEY 约束吗？

答：在 SQL Server 中，UNIQUE 和 PRIMARY KEY 约束用来实现唯一性的机制是唯一索引。因为 SQL Server 2000 支持在计算列上的索引，所以您可以在计算列上创建 UNIQUE 和 PRIMARY KEY 约束。

在计算列上定义 UNIQUE 约束如下面的实例所示是很简单的：

CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)

但是，如果在计算列上定义了如下 PRIMARY KEY：

CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)

将接收到下列错误：

Server:Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'T2'.
Server:Msg 1750, Level 16, State 1, Line 1
Could not create constraint.See previous errors.

因为主键约束，所以 SQL Server 需要您保证计算的结果不为 NULL。计算列中的计算可以上溢（例如，将 1 添加为最大的整数时）或下溢（从最小的整数抽取 1 时），其他计算会导致被零除错误。但是，如果 ARITHABORT（它决定了发生上溢或被零除错误时查询是否终止）和 ANSI_WARNINGS（它为多个错误条件指定了 ANSI SQL-92 标准行为）会话设置关闭，而不是终止查询，计算可能会具有 NULL 结果。

在实际情况中，当 ARITHABORT 或 ANSI_WARNINGS 设置关闭时，不能在计算列中创建索引，或将值插入到在计算列上具有索引的表中，因为 SQL Server 会检测这种尝试并返回一个错误。但是 SQL Server 仍会要求您确保不会产生 NULL 值。该技巧可以使用 ISNULL() 函数绕过对计算列的计算，如果计算结果为空，将应用一个替换值：

CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)

检测被阻止的连接

Q. 我如何通过编程来检测某个指定的连接是否被锁定?
A. 当一个连接请求一个被另一个连接加锁的对象时，该连接被锁定。你可以使用系统存储过程sp_lock来检索关于 SQL Server中当前的锁的信息，并且你可以使用服务器处理ID(SPID，Server Process ID)来过滤sp_lock返回的信息。要确定某个指定的处理是否在等待释放锁定的资源，你可以运行下面的sp_GetBlockInfo过程。

注意: 你必须在超时之前运行该过程。

USE master
GO

CREATE PROCEDURE sp_GetBlockInfo
@BlockedSPID as int
AS
IF EXISTS (select *
FROM master.dbo.syslockinfo
WHERE req_spid = @BlockedSPID
AND req_status = 3)

SELECT sli1.req_spid AS SPID,
SUBSTRING (u.name, 1, 8) As Mode,
DB_NAME(sli1.rsc_dbid) AS [Database],
OBJECT_NAME(sli1.rsc_objid) AS [Table],
sli1.rsc_Text AS [Resource]
FROM master.dbo.syslockinfo sli1
JOIN master.dbo.spt_values u
ON sli1.req_mode + 1 = u.number
AND u.type = 'L'
JOIN
master.dbo.syslockinfo sli2
ON sli1.rsc_dbid = sli2.rsc_dbid
AND sli1.rsc_objid = sli2.rsc_objid
AND sli1.rsc_text = sli2.rsc_text
WHERE sli2.req_spid = @BlockedSPID
AND sli1.req_status = 1
AND sli1.req_spid <> @BlockedSPID
AND sli2.req_status = 3
ELSE

SELECT CAST(1 as int) AS SPID,
SUBSTRING ('', 1, 8) AS Mode,
DB_NAME(NULL) AS [Database],
OBJECT_NAME(NULL) AS [Table],
CAST(NULL AS nchar(32)) AS [Resource]
WHERE 1=2

GO

sp_GetBlockInfo过程告知你锁模式、锁定资源的数据库和对象名称，以及在锁定链情况下，哪一个SPID是封锁着。如果该进程没有被封锁，sp_GetBlockInfo将返回一个空记录集。

你还可以通过检查1222号错误(“Lock request time out period exceeded.(锁请求时间超时)”)来检测锁。LOCK_TIMEOUT设置控制一个进程在超时前，等待锁释放的时间。当锁超时发生时，SQL Server将向应用程序发送1222号错误。在SQL Server 7.0中，这个错误将中止该语句，但并不会引起批处理的回滚，这样你可以查找Transact-SQL系统变量@@ERROR，然后确定是否存在锁。要了解有关@@ERROR的细节，请参阅SQL Server Books Online (BOL) 中的“Customizing the Lock Time-out”一节。

SQL Server 2000文档描述了1222号错误在SQL Server 7.0中的行为；但是，在SQL Server 2000 RTM(release to manufacturing)中，1222号错误将引起批处理回滚和中止，这样将会消除@@ERROR变量。 微软公司已经认识到了这个问题，并在SQL Server 2000 Service Pack 1 (SP1)中提供了一个Hotfix程序。这个Hotfix程序提供了一种将 SQL Server 2000还原到 SQL Server 7.0行为的方法。要了解有关这个热修正程序的更多信息，请参阅微软知识库文章"FIX: LOCK_TIMEOUT Causes Transaction to Roll Back and @@ERROR Does Not Capture Error 1222."

—SQL Server MVP

 

现实相关性

Q. 当我使用sp_depends系统存储过程来显示我的数据库对象相关性时，与在 SQL Server Enterprise Manager中使用Display Dependencies窗口的结果不同。为什么?
A. Enterprise Manager执行无文档说明的存储过程sp_Msdependencies，这是一个比有文档说明的sp_depends 存储过程更高级的过程。此外，sp_Msdependencies并没有被限制为第一层相关性。

你可以运行下列语句来获得对sp_MSdependencies所支持的参数的完整描述：

EXEC sp_MSdependencies '?'

这里有一些Enterprise Manager生成和SQL Server Profiler捕获的代码例子。下面的代码显示了第一层相关性：

/* sp_MSdependencies — First level only */
-- Objects that are dependent on the specified object
EXEC sp_MSdependencies N'[dbo].[Order Details Extended]', null, 1315327
-- Objects that the specified object is dependent on
EXEC sp_MSdependencies N'[dbo].[Order Details Extended]', null, 1053183

第二个例子显示了sp_MSdependencies 所有层次的相关性:

/* sp_MSdependencies - All levels */
-- Objects that are dependent on the specified object
EXEC sp_MSdependencies N'[dbo].[Order Details Extended]', null, 266751
-- Objects that the specified object is dependent on
EXEC sp_MSdependencies N'[dbo].[Order Details Extended]', null, 4607
为进行比较，你使用sp_depends 的命令是
EXEC sp_depends N'[dbo].[Order Details Extended]'

—SQL Server MVP

WHERE 子句中包括的变量

 

 

 

问： 我遇到了一个 SQL Server 7.0 性能问题。我有一个表在 OWNER_PER_ID 和 APPT_REPT_FLG 列有非聚集索引，并在 ROW_STATUS 和 OWNER_PER_ID 列有聚集索引的表。在执行 SELECT 语句从表中检索数据之前，声明了变量 @P1 到 @P5，并且将它们的值分别设置为 Y、Y、N、I-K56 和 Y。在使用参数执行该语句时，Query Analyzer（查询分析器）使用非聚集索引，性能很差。但是，在运行相同的语句并使用硬编码 Y 而不使用变量 @P2 时，Query Analyzer 使用聚集索引，并且性能优异。在 SELECT 语句的 WHERE 子句中使用变量 @P2 和硬编码 Y 有什么区别？

答：SQL Server 选择的计划取决于 APPT_REPT_FLG 索引上的统计数据。在 WHERE 子句中有变量时，SQL Server 假设一个标准数量的匹配列。但是，当查询优化程序具有一个实际值（如上面的 Y）时，可以通过检查索引上的统计数据来确定实际大概需要应用多少行的限制。例如，在 SQL Server 7.0 系统上，如果有一个变量与该数目匹配，则查询优化程序假设 10% 的行将返回。在查询优化程序将所有信息称为可能时（其中包括 WHERE 子句中的值），您将获得更好的结果。

-Richard Waymire, Microsoft Group Program Manager for SQL Server Management Tools

内嵌或嵌入SQL和存储过程之对比
2001年6月4日

问：为什么我的内嵌SQL或嵌入SQL 要比我的存储过程运行的快？

答：重编译可能是存储过程运行比较慢的原因。要确认这一点，您需要做一些性能测试，例如：看一下每种类型的查询和调用存储过程的“showplan”情况，并比较查询计划的高速缓存命中率和高速缓存缺失率。您还可以试着在存储过程内部为引用表、视图和过程写出对象拥有者。如下面例子所示：

SELECT * FROM dbo.mytable

这样有助于您重新使用计划并防止高速缓存缺失。

管理SQL Server 2000、 7.0和6.x 中的char值
2001年4月

问：SQL Server 2000、7.0和6.x 管理char值的方法不同吗？如果那样的话， 我能通过使用适当的数据库兼容级别来弥补这种不同吗？

答：在从SQL Server 6.x向SQL Server 2000 或 7.0转移时，使用6.0 和65 的兼容模式可以解决您可能会遇到的大多数问题。SQL Server Books Online 包含了超过90页的有关兼容问题的信息。但是Books Online 不会帮您管理char值。如果您运行以下的脚本：

DECLARE @test char (5)
SELECT @test = 'a'

SELECT right(@test, 1)

在SQL Server 7.0 下运行会得到与SQL Server 6.5下不同的结果，并且SQL Server 6.5会忽略您使用的兼容级别。SQL Server 2000 和 7.0 把可为空的char处理为char值，而SQL Server 6.x 把所有的可为空的char处理为varchar值。

查询优化器如何使用统计信息

问题：SQL Server 7.0中的查询优化器何时对复合型索引的分发内容统计进行检测，这种检测过程涉及索引中每一列的统计信息还是紧紧涉及第一列的统计信息（假设相应的表中只包含索引统计，而不包含列统计）？如果查询优化器考虑索引中每一列的统计信息，那么，当某一索引的最后一列中拥有合适的统计信息时，查询优化器是否能够选择这种索引？请考虑以下示例（假设相应的表中包含数据）：

CREATE TABLE test_table

(entry_date   datetime,

row_id        int,

first_name    char(30),

last_name     char(30))

CREATE INDEX test_table_idx on test_table

(last_name, first_name, row_id)

SELECT ROW_id

FROM test_table

WHERE row_id=10

GO

如果查询优化器对复合型索引中每一列的分发内容统计进行检测，那么，SQL Server能否借助该表的索引来完成查询?

解答：SQL Server只为多列索引中的第一列提供直方图，并为所有初始前缀提供密度信息。您所列举的示例拥有三种密度：一种针对last_name；一种针对last_name与first_name的组合；另一种则针对last_name、first_name与row_id的组合。服务器有可能仍旧选择对索引而非数据表进行扫描，之所以这样做，是因为扫描索引所耗费的I/O资源相对比较少，而并非由于row_id列的统计信息所至。除了在以上所显示的索引中对数据表进行扫描外，查询优化器还将检测相应表中的其它索引。针对上述查询内容，最为理想的索引应以row_id作为首列，并且包含last_name和first_name列。如果row_id列取值唯一（或者几乎唯一），那么，建立一个只包含row_id的单列索引也将是一种良好的规划方式。这种情况下，无论是否具备相应的聚集索引，索引查找都将依据从基表中所取回的first_name与last_name值加以确定。

开发技巧

Q. 我如何将作为字符串保存的IP地址变为二进制数值？
A. 对于SQL Server 2000，你可以使用下列函数:

CREATE FUNCTION dbo.IPAddrStr2Bin(@strIP varchar(15), @Validate bit = 1)
RETURNS binary(4)
AS
BEGIN
IF @Validate = 1
BEGIN
-- only digits and dots
IF @strIP LIKE '%[^.0-9]%' RETURN (NULL)
-- number of dots must be 3
IF LEN(@strIP) - LEN(REPLACE(@strIP, '.', '')) != 3 RETURN (NULL)
-- all octets must be specified
IF @strIP NOT LIKE '%_%.%_%.%_%.%_%' RETURN (NULL)
END
DECLARE @oct1 binary(1),
@oct2 binary(1),
@oct3 binary(1),
@oct4 binary(1)
SET @oct1 = CAST(CAST(LEFT(@strIP, CHARINDEX('.', @strIP) - 1) AS int) AS
binary(1))
SET @oct2 = CAST(CAST(SUBSTRING(@strIP, CHARINDEX('.', @strIP) + 1,
CHARINDEX('.', @strIP, CHARINDEX('.', @strIP) + 1) - CHARINDEX('.', @strIP)
- 1) AS int) AS binary(1))
SET @oct3 = CAST(CAST(SUBSTRING(@strIP, CHARINDEX('.', @strIP,
CHARINDEX('.', @strIP) + 1) + 1, (LEN(@strIP) - CHARINDEX('.',
REVERSE(@strIP)) + 1) - (CHARINDEX('.', @strIP, CHARINDEX('.', @strIP) + 1))
- 1) AS int) AS bin