about functions. - 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 2000 Forums

SQL Server Development (2000)

about functions.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-20 : 07:20:03

shanthi writes "hello

am trying to create a function isupin ,will pass one varchar of six digits.it is valid only first character should be a letter and the others digit and also first three is the letters and the last three is the digits else its not a valid.
this is my code

create FUNCTION jpr.main.pcare.isupin
(@tupin nvarchar(6))
returns nvarchar(10)
as
begin
declare @tstr nvarchar(10)
if tupinin not like '[a-z][a-z][a-z][0-9][0-9][0-9]' AND tupin not like'[a-z][0-9][0-9][0-9][0-9][0-9]'
begin
set @tstr ="Not valid"
return ( @tstr )
end
else if tupinin not like '[a-z][a-z][a-z][0-9][0-9][0-9]' or tupin not like'[a-z][0-9][0-9][0-9][0-9][0-9]'
BEGIN
SET @tstr ="valid"
return ( @tstr )
end
end

while executing the errors coming are
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 178, Level 15, State 1, Line 10
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 178, Level 15, State 1, Line 15
A RETURN statement with a return status can only be used in a stored procedure.

please help me................ am now only started to know about SQL SERVER 7
Thank you."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-20 : 10:39:04

first of all you need to create the function from within the target database and use a two part name <owner>.<fnName>. Also, you are referencing tupinin and tupin from within your function which are not column names or variables. Finally, you need to have a RETURN statement as the final statement. Try this:


create FUNCTION dbo.isupin (@tupin nvarchar(6))
returns nvarchar(10)
as
begin
	declare @tstr nvarchar(10)
	set @tstr = 'Not valid'

	if @tupin like '[a-z][a-z][a-z][0-9][0-9][0-9]' 
	or @tupin like '[a-z][0-9][0-9][0-9][0-9][0-9]'
	begin
		set @tstr ='valid'
	end

	return @tstr
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources