create function, failed - 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

General SQL Server Forums

New to SQL Server Administration

create function, failed

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-27 : 14:44:49

what is the syntax error ? no return value ?


use testdb;

create function test_fn()
as
begin
	print 'test';	
end

error is


Incorrect syntax near the keyword 'as'.

Sachin.Nand

2937 Posts

Posted - 2011-10-27 : 14:47:27

A function is supposed to return something.I cannot see your function doing that.

PBUH

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-27 : 14:52:35

if I don't want to return anything, can I just 'return void' ?

If I changed to this, still doesn't work


create function test_fn()
returns int
as
begin
	print 'test';
	return 1;	
end

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 15:14:30

you can't say PRINT in a UDF

Void?

Do you know what Books Online (BOL) is?

This a table valued function. you can also create scalar functions (and aggregate?)



CREATE FUNCTION [dbo].[udf_Table](@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(Parameter varchar(255))

AS 

/*
 SELECT * FROM dbo.udf_Table( 'a|~|b|~|c', '|~|')
*/

BEGIN
	DECLARE @x int, @Parameter varchar(255)

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(Parameter) 
		SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		

		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+LEN(@Delim), LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
	  END 
	INSERT INTO @table(Parameter) SELECT @ParmList
	RETURN
END


GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-27 : 15:32:18

quote:
Originally posted by java148

if I don't want to return anything, can I just 'return void' ?

If I changed to this, still doesn't work

create function test_fn()
returns int
as
begin
print 'test';
return 1;
end

Thanks

A print wont work in a UDF.Without a print the above function will work.

PBUH

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-27 : 22:13:34

if function can't use print , then how to debug ?

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-10-28 : 00:07:33

declare @status int

exec @status= dbo.test_fn

if @status=1 print 'test'

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources