Problem creating function

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 16-Jan-11 21:24pm

balongi

Updated 16-Jan-11 21:33pm

Nuri Ismail

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Hiren solanki · Answer 1 · 2011-01-16T21:52:00

Solution 2

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;

Posted 16-Jan-11 21:52pm

Hiren solanki

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

Baji Jabbar · Answer 2 · 2011-01-16T21:39:00

Solution 1

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>

Posted 16-Jan-11 21:39pm

Baji Jabbar

Updated 16-Jan-11 22:07pm

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