Function Creation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function Creation

Author

Topic

andreas.zaras
Starting Member

2 Posts

Posted - 2011-03-09 : 04:10:44

Hello,

I am trying to create a function that counts the characters between two slashes in a string. I am writing the follwings lines:

create function priceobserver.fourth_node
(@variable nvarchar(100))
returns int(50)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @length int(50)
select @length=(charindex('/',@variable,charindex('/',@variable,charindex('/',@variable,charindex('/',@variable)+1)+1)+1)-1),(charindex('/',@variable,charindex('/',@variable,charindex('/',@variable,charindex('/',@variable)+1)+1)+1)-1)
RETURN(ISNULL(@length,0))
end
GO

What is worng woth that?
I get the following error message:
Msg 141, Level 15, State 1, Procedure fourth_node, Line 8
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Thanks in advance

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-09 : 04:16:01

Replace the SELECT with SET

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

andreas.zaras
Starting Member

2 Posts

Posted - 2011-03-09 : 04:23:46

Hello,

Thanks for your answer. The previous error is gone but i get the following:

Msg 102, Level 15, State 1, Procedure fourth_node, Line 8
Incorrect syntax near ','.

Though if i run seperately the following (which is the same) i get correct result.

select (charindex('/',node_name,charindex('/',node_name,charindex('/',node_name,charindex('/',node_name)+1)+1)+1)-1)-(charindex('/',node_name,charindex('/',node_name,charindex('/',node_name)+1)+1)+2)
from priceobserver.getproductstructurenameshierarchy()

ANy ideas?

Thnaks again

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-09 : 04:40:10

You have a comma in your function but a minus in your working query:


create function priceobserver.fourth_node
	(@variable nvarchar(100))
returns int(50)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @length int(50)
set @length = 
	(charindex('/',@variable,charindex('/',@variable,charindex('/',@variable,charindex('/',@variable)+1)+1)+1)-1), <-- See here
	(charindex('/',@variable,charindex('/',@variable,charindex('/',@variable,charindex('/',@variable)+1)+1)+1)-1)
RETURN(ISNULL(@length,0))
end

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-09 : 04:43:21

And it seems you need to brush up on your copy/paste skills.

This:
(charindex('/',@variable,charindex('/',@variable,charindex('/',@variable,charindex('/',@variable)+1)+1)+1)-1)-
(charindex('/',@variable,charindex('/',@variable,charindex('/',@variable,charindex('/',@variable)+1)+1)+1)-1)

is still pretty different from this

(charindex('/',node_name,charindex('/',node_name,charindex('/',node_name,charindex('/',node_name)+1)+1)+1)-1)-
(charindex('/',node_name,charindex('/',node_name,charindex('/',node_name)+1)+1)+2)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources