CRLF序列作为存储过程中的默认参数值

I want a stored procedure in SQL Server 2008 to have a default value specified for one of its parameters. I want this value to be a CRLF sequence. Obviously SQL Server does not allow me to do this:

我希望SQL Server 2008中的存储过程具有为其中一个参数指定的默认值。我希望这个值是一个CRLF序列。显然SQL Server不允许我这样做：

CREATE PROCEDURE SomeProc
   @arg1 nvarchar(2) = CHAR(13) + CHAR(10),
   @arg2
...

I know I could write this sequence simply as:

我知道我可以简单地写这个序列：

CREATE PROCEDURE SomeProc
   @arg1 nvarchar(2) = '
',
   @arg2
...

but it seems... not quite right. What if I send this script to someone via e-mail and CRLF gets converted to LF? Or some space is accidentally added? Or finally, what if I want some other sequence (let it be any Unicode character)?

但似乎......不太正确。如果我通过电子邮件将此脚本发送给某人并且CRLF转换为LF，该怎么办？还是偶然添加了一些空间？或者最后，如果我想要一些其他序列（让它成为任何Unicode字符）怎么办？

Using variable to store default value is not an option, as there is no place to declare and initialize variable since CREATE PROCEDURE must be first statement in a batch.

使用变量存储默认值不是一个选项，因为没有地方可以声明和初始化变量，因为CREATE PROCEDURE必须是批处理中的第一个语句。

Also using EXEC on statement string is obviously too troublesome.

在语句字符串上使用EXEC显然太麻烦了。

Is there any better way to allow sequence of non-printable ASCII/Unicode characters as default value for stored procedure parameter?

有没有更好的方法允许不可打印的ASCII / Unicode字符序列作为存储过程参数的默认值？

1 个解决方案

#1

How about this:

这个怎么样：

CREATE PROCEDURE dbo.SomeProc
   @arg1 nvarchar(2) =  0x0D000A00 --CHAR(13) + CHAR(10)

Running the code below returns "is equal" :

运行以下代码返回“相等”：

CREATE PROCEDURE dbo.SomeProc
   @arg1 nvarchar(2) =  0x0D000A00 
AS    
declare @v nvarchar(2) = CHAR(13) + CHAR(10)

IF @arg1 = @v
select 'is equal'
ELSE
select 'is not equal'
GO

EXEC dbo.SomeProc

To work out what hex sequence you should use, you can do something like:

要计算出你应该使用的十六进制序列，你可以这样做：

declare  @arg1 nvarchar(2) = CHAR(13) + CHAR(10)
select cast(@arg1 as varbinary(max))

which returns:

0x0D000A00

#1