Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can't declare variable in function MS SQL

I'm newbie in SQL and trying to create function in MS SQL 2008R2, but can't declare variable inside function. What's wrong with this code?

CREATE FUNCTION denominator() RETURNS int
BEGIN
    DECLARE @Count;
    -- Some logic here
END;

GO
SELECT dbo.denominator()

DROP FUNCTION denominator

I'm getting that kind of errors:

Msg 102, Level 15, State 1, Procedure denominator, Line 3
Incorrect syntax near ';'.
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.denominator", or the name is ambiguous.
like image 492
vmeln Avatar asked Jan 11 '13 14:01

vmeln


People also ask

Can I DECLARE variable in function SQL?

The DECLARE statement is used to declare a variable in SQL Server. In the second step, we have to specify the name of the variable. Local variable names have to start with an at (@) sign because this rule is a syntax necessity. Finally, we defined the data type of the variable.

How do you DECLARE a variable in a function?

Declaring variables with the var keyword If the variable is declared outside of any functions, the variable is available in the global scope. If the variable is declared within a function, the variable is available from its point of declaration until the end of the function definition.

How do you DECLARE a variable in a table valued function in SQL Server?

Syntax. If we want to declare a table variable, we have to start the DECLARE statement which is similar to local variables. The name of the local variable must start with at(@) sign. The TABLE keyword specifies that this variable is a table variable.

Can we DECLARE variable in CTE in SQL Server?

A CTE is an expression. In SQL, expressions are contained within statements, and never the other way around. Unless you are trying to make a view, you should be able to just put your DECLARE statements before the statement that contains your CTE's. As long as they are in the same batch, this should work.


2 Answers

you need to write like this , data type of variable is missing

DECLARE @Count int;
like image 116
Pranay Rana Avatar answered Sep 21 '22 09:09

Pranay Rana


you're declaring of @Count has no data type, you should provide it.

DECLARE @Count int
like image 43
John Woo Avatar answered Sep 20 '22 09:09

John Woo