Click here to Skip to main content
15,902,931 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi

I'm creating a function in SQL Server 2005. Here is my code:
SQL
create function hello
(
 declare @var bigint;
)
as
set @var = (select * from tbldealres)


But the following error come:
Msg 156, Level 15, State 1, Procedure hello, Line 5
Incorrect syntax near the keyword 'declare'.
Msg 102, Level 15, State 1, Procedure hello, Line 6
Incorrect syntax near ')'.


Please, help me on this.
Posted
Updated 16-Jan-11 21:33pm
v2

You're getting error because you're violating SQL function Syntax, See function syntax HERE[^] for more.

You can further improve your function like below.

SQL
CREATE FUNCTION dbo.hello()
returns bigint
AS
BEGIN
 declare @var bigint;
 set @var = (select COUNT(*) from TestXML)
 return (@var)
END;
 
Share this answer
 
Comments
Baji Jabbar 17-Jan-11 4:04am    
Good one +5
Hiren solanki 17-Jan-11 4:06am    
Thanks baji.
Nuri Ismail 17-Jan-11 4:45am    
Good answer. +5
Avoid the semicolon (;)
SQL
create function hello
returns bigint

as 
begin
declare @var bigint
set @var = select <coloumn name=""> from tbldealres ( where clause optional to return a single column) 
return @var 
end
</coloumn>
 
Share this answer
 
v2
Comments
balongi 17-Jan-11 3:44am    
agian same error come
Baji Jabbar 17-Jan-11 3:55am    
U are assigning the whole result set (multiple rows) to a single variable. So what you expect in the @var variable.
Hiren solanki 17-Jan-11 3:55am    
your function will result into error. see my answer.
Baji Jabbar 17-Jan-11 4:03am    
yes Hiren , you are correct , I am improving my answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900