error with function

Question

Post reply

error with function

Deepthy

SSCommitted

Points: 1823
More actions
November 5, 2010 at 10:41 am

#228811

Hi all,
here is my function
create function test(@Country NVARCHAR(100),@Addressline NVARCHAR(100))
returns nvarchar(100)
AS
declare @result nvarchar(100)
set @result= (select (SUBSTRING(@ADDRESSLINE, nullif(patindex('% ' + s.format + ' %', ' ' + @addressline + ' '), 0), len(format)))
FROM sample s
WHERE s.Country = @COUNTRY )
return @result
but is giving error incorrect suntaxt near declare. could not figure out what the error is.
Any help is really appreciated

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 26551 More actions · Answer 1

How about

create function test(@Country NVARCHAR(100),@Addressline NVARCHAR(100))

returns nvarchar(100)

AS

Begin

declare @result nvarchar(100)

set @result= (select (SUBSTRING(@ADDRESSLINE, nullif(patindex('% ' + s.format + ' %', ' ' + @addressline + ' '), 0), len(format)))

FROM sample s

WHERE s.Country = @COUNTRY )

return @result

END

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Deepthy SSCommitted Points: 1823 More actions · Answer 2

Thanks for your reply. Thats working.

but when i run the query

UPDATE sample_table

SET CLEAN_POSTCODE = dbo.test(country,postcode) .

I am getting the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The table sample contain more than one record. How can I update each row of sample_table with clean_postcode.

Thanks in advance

LutzM SSC Guru Points: 107049 More actions · Answer 3

Michael answered the original question but I'd like to go one step further:

Since all you need to return is the result of a select statement, you could use an iTVF (inline table-valued function):

CREATE FUNCTION itvf_test(@Country NVARCHAR(100),@Addressline NVARCHAR(100))

RETURNS TABLE

AS

RETURN

(

SELECT (SUBSTRING(@ADDRESSLINE, NULLIF(PATINDEX('% ' + s.format + ' %', ' ' + @addressline + ' '), 0), LEN(format)))

FROM sample s

WHERE s.Country = @COUNTRY

)

-- to reference it:

-- SELECT * FROM itvf_test('random country','random Addressline')

This kind of function actually works like a table, whereas the scalara function is used like a function against a column (for differences how to call a iTVF vs. a scalar-valued function please see BOL, the SQL Server help system usually installed together with SQL Server).

The advantage is simple: performance. Instead of callnig the function separately for each row, it will be used only once against the dataset in question.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Michael L John One Orange Chip Points: 26551 More actions · Answer 4

It seems as if you do not need a function, but an update of the field.

Are you trying to correct some values in a table?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

LutzM SSC Guru Points: 107049 More actions · Answer 5

I second Michael.

Why not use the update directly?

UPDATE sample_table

SET CLEAN_POSTCODE = SUBSTRING(postcode, NULLIF(PATINDEX('% ' + format + ' %', ' ' + postcode + ' '), 0), LEN(format))

Edit: or are you trying to build a function to use it in a computed column? If so, instead of calling the function, use your substring statement directly to define the computed column.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Deepthy SSCommitted Points: 1823 More actions · Answer 6

I want to use the function because the sample table may be updated in future with new formats, so any change in the sample table should also affect the function and generate a clean_postcode.