SQL查询(在sql server 2008上工作,不在sql server 2005上) [英] SQL query( work on sql server 2008 r2 not on sql server 2005)
问题描述
以下查询在sql server 2008 R2上运行良好
创建功能[dbo]。[排序功能]
(
@column varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
声明@num varchar(50)
声明@space varchar(50)
声明@index int = 1
设置@num = LEFT(SUBSTRING(@column,PATINDEX('%[0-9 .-]%',@ column),8000),PATINDEX('%[^ 0-9 .-]%',SUBSTRING (@column,PATINDEX('%[0-9 .-]%',@ column),8000)+'X') - 1)
set @space = replicate('',20 - len(@num))
返回替换(@column,@ num,@ space + @num)
END
--------------------------------------------- -------------------------
通过dbo.sortingfunction(键)从样本表顺序中选择
在sql server 2005上它说
消息139,等级15,状态1 ,程序分类功能,第0行
Canno t为本地变量分配一个默认值。
请帮助
Below query work well on sql server 2008 R2
create FUNCTION [dbo].[sortingfunction]
(
@column varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
Declare @num varchar(50)
Declare @space varchar(50)
Declare @index int = 1
set @num = LEFT(SUBSTRING(@column, PATINDEX('%[0-9.-]%', @column), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(@column, PATINDEX('%[0-9.-]%', @column), 8000) + 'X')-1)
set @space = replicate(' ', 20 - len(@num))
return replace(@column, @num, @space + @num)
END
----------------------------------------------------------------------
select from sampletable order by dbo.sortingfunction(key)
while on sql server 2005 it saying that
Msg 139, Level 15, State 1, Procedure sortingfunction, Line 0
Cannot assign a default value to a local variable.
please help
推荐答案
更改此行:
Change this line:
声明@index int = 1
Declare @index int = 1
对此:
To this:
声明@index int
设置@index = 1
Declare @index int
Set @index = 1
在SQL 2005之后添加了初始化语法。
The initialisation syntax was added after SQL 2005.
这篇关于SQL查询(在sql server 2008上工作,不在sql server 2005上)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!