Can not declare variable in function

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 2005 Forums

Transact-SQL (2005)

Can not declare variable in function

Author

Topic

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-29 : 08:49:41

Hi all,
I cant declare a variable in a user defined Sql function
The code is;


ALTER FUNCTION [PERSONEL].[FN_search] 
	-- Add the parameters for the stored procedure here
(	@PageNum int,
    @PageSize int)

RETURNS TABLE

AS
RETURN
(
	declare @RowNumber int

	WITH dinamikSearch As
    (
        SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY id DESC), 
               id,name
        FROM dbo.vi_person
    )
       .
       .
       .
       .

ERRORS ARE

Incorrect syntax near the keyword 'declare'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near ')'.

What the code is doing is not much important, when I delete declare statement there is no error. What am I supposed to do?

Thanks in advance,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 08:57:30

You have written a inline table valued function.
Read about them in Books Online.

Or search SQLTeam for alternatives for your function.

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-29 : 09:15:07

don't need to use CTE

ALTER FUNCTION [PERSONEL].[FN_search] 
	-- ADD the parameters FOR the stored PROCEDURE here
(	
	@PageNum int,
    	@PageSize int
)
RETURNS TABLE
AS
RETURN
(
	SELECT	RowNumber, id, name
	FROM
    	(
        	SELECT 	[RowNumber] = ROW_NUMBER() OVER(ORDER BY id DESC), 
               		id,
               		name
        	FROM dbo.vi_person
        )
	WHERE	[RowNumber]	>  (@PageNum - 1) * @PageSize
	AND	[RowNumber]	<= (@PageNum) 	  * @PageSize
    )
)

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 09:27:35

[code]ALTER FUNCTION PERSONEL.FN_search
(
@PageNum int,
@PageSize int
)
RETURNS TABLE
AS
RETURN (
SELECT TOP (@PageSize)
FROM (
SELECT TOP (@PageNum * @PageSize)
ID,
Name
FROM dbo.vi_person
ORDER BY ID DESC
) AS d
ORDER BY ID ASC
)[/code]

E 12°55'05.63"
N 56°04'39.26"

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-29 : 09:28:04

The CTE is not the problem. I need to declare some variables inside the function. It can be a CTE or the other one. But still, I cant declare a variable.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-29 : 09:29:50

The DECALRE statement should be between the AS & the RETURN.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 09:31:06

Did you read about INLINE TABLE VALUED FUNCTIONS?
You can't do that.

Use a normal table function, like this

ALTER FUNCTION [PERSONEL].[FN_search] 
	-- Add the parameters for the stored procedure here
(	@PageNum int,
    @PageSize int)

RETURNS @MyTable TABLE (RowNumber INT, ID INT, Name VARCHAR(20))
AS
BEGIN
	declare @RowNumber int

	;WITH dinamikSearch
	As (
		SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY id DESC), 
			id,name
		FROM dbo.vi_person
		)
	...

	INSERT	@MYTable
	SELECT rownumber, id, name
	from dinamikSearch

	RETURN
END

E 12°55'05.63"
N 56°04'39.26"

ramsesiitr
Starting Member

22 Posts

Posted - 2008-08-29 : 09:41:41

Thanks Peso, that should be it.
I was reasding about ITVF but you are faster than me mate...

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-31 : 19:04:41

Peso - thanks for pointing this out. I have been using IFs to solve an architectual issue & by coincidence I had not used any local vars. This will save me time when I encounter the need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources