DECLARE error and initialization of table var

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 Programming

DECLARE error and initialization of table var

Author

Topic

tash
Starting Member

12 Posts

Posted - 2008-08-01 : 13:15:22

I don't understand why I obtain error "Incorrect syntax near the keyword 'DECLARE'." while declaring my integer variable @TransactionNumber:


CREATE FUNCTION EntFunction 
(@ItemName varchar(100))
returns table
as
return

DECLARE @TransactionNumber int;   <-----HERE!
DECLARE @DFreqTable table(
    DName varchar(100) NOT NULL,
    DFreq int NOT NULL);
DECLARE @EntTable table (
	EffName varchar(100) NOT NULL,
	EntValue float NOT NULL);
SELECT @TransactionNumber = (SELECT count(TransactionID) FROM ...etc.

And another question:
I've created table @EntTable.
I want to assign value to the first field EffName in this table, and select value for the second field from another table, something like this:


INSERT INTO @EntTable
@ItemName, SELECT sum(DFreq) FROM @DFreqTable

but it doesn't work with @ItemName before SELECT statement, only with SELECT.
How can I do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:21:39

you cant have declare statements if you're trying to create inline table function. i think what you want is a multi statement table valued function. something like this


CREATE FUNCTION EntFunction 
(@ItemName varchar(100))
returns @EntTable table (
	EffName varchar(100) NOT NULL,
	EntValue float NOT NULL)
as
return

DECLARE @TransactionNumber int;   <-----HERE!
DECLARE @DFreqTable table(
    DName varchar(100) NOT NULL,
    DFreq int NOT NULL);

SELECT @TransactionNumber = (SELECT count(TransactionID) FROM ...etc.
...

and you can return only one table (@EntTable in this case).

Also your select should be like this


INSERT INTO @EntTable
SELECT @ItemName, sum(DFreq) FROM @DFreqTable

tash
Starting Member

12 Posts

Posted - 2008-08-01 : 13:33:31

quote:
Originally posted by visakh16

you cant have declare statements if you're trying to create inline table function. i think what you want is a multi statement table valued function. something like this

CREATE FUNCTION EntFunction
(@ItemName varchar(100))
returns @EntTable table (
EffName varchar(100) NOT NULL,
EntValue float NOT NULL)
as
return

DECLARE @TransactionNumber int; <-----HERE!
DECLARE @DFreqTable table(
DName varchar(100) NOT NULL,
DFreq int NOT NULL);

SELECT @TransactionNumber = (SELECT count(TransactionID) FROM ...etc.
...

and you can return only one table (@EntTable in this case).

Also your select should be like this

INSERT INTO @EntTable
SELECT @ItemName, sum(DFreq) FROM @DFreqTable

Thanks, You are absolutely right, That's what I want to obtain.
The second problem is successfully solved, but with the first one I have this error "Incorrect syntax near 'RETURN'."
in this code:


CREATE FUNCTION EntFunction 
(@ItemName varchar(100))
returns @EntTable table (
	EffName varchar(100) NOT NULL,
	EntValue float NOT NULL)
as
return   <-------HERE!

DECLARE @TransactionNumber int;
DECLARE @DFreqTable table(
    DName varchar(100) NOT NULL,
    DFreq int NOT NULL);....etc.

am I inattentive? *DON'T KNOW*

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:36:29

quote:
Originally posted by tash

quote:
Originally posted by visakh16

you cant have declare statements if you're trying to create inline table function. i think what you want is a multi statement table valued function. something like this

CREATE FUNCTION EntFunction
(@ItemName varchar(100))
returns @EntTable table (
EffName varchar(100) NOT NULL,
EntValue float NOT NULL)
as
return
DECLARE @TransactionNumber int; <-----HERE!
DECLARE @DFreqTable table(
DName varchar(100) NOT NULL,
DFreq int NOT NULL);

SELECT @TransactionNumber = (SELECT count(TransactionID) FROM ...etc.
...

and you can return only one table (@EntTable in this case).

Also your select should be like this

INSERT INTO @EntTable
SELECT @ItemName, sum(DFreq) FROM @DFreqTable

Thanks, You are absolutely right, That's what I want to obtain.
The second problem is successfully solved, but with the first one I have this error "Incorrect syntax near 'RETURN'."
in this code:

CREATE FUNCTION EntFunction
(@ItemName varchar(100))
returns @EntTable table (
EffName varchar(100) NOT NULL,
EntValue float NOT NULL)
as
~~return~~ <-------HERE!

DECLARE @TransactionNumber int;
DECLARE @DFreqTable table(
DName varchar(100) NOT NULL,
DFreq int NOT NULL);....etc.

return

am I inattentive? *DON'T KNOW*

the return should be last statement in function. put it as last statement before END

tash
Starting Member

12 Posts

Posted - 2008-08-01 : 14:01:07

Thanks again! Now I don't have errors. One more question from beginner (it is my first UDF)

I have this sql query (so it is whole function, I've created):


use AR;
go
CREATE FUNCTION EntFunction 
(@ItemName varchar(100))
returns @EntTable table (
	EffName varchar(100) NOT NULL,
	EntValue float NOT NULL)
as

BEGIN
DECLARE @TransactionNumber int;
DECLARE @DFreqTable table(
    DName varchar(100) NOT NULL,
    DFreq int NOT NULL);
SELECT @TransactionNumber = (SELECT count(TransactionID) FROM AR.dbo.EffectsTransactions WHERE EffectName=@ItemName) 

INSERT INTO @DFreqTable 
SELECT
	dr.DName,
	count(dr.DName)/@TransactionNumber AS DFreq
            FROM
	        AR.dbo.DTransactions dr, AR.dbo.EffectsTransactions ef

WHERE
	ef.EffectName=@ItemName
	AND dr.TransactionID=ef.TransactionID
GROUP BY dr.DName
INSERT INTO @EntTable
SELECT @ItemName, sum(DFreq*LOG(DFreq)) FROM @DFreqTable
RETURN
END

I executed it as sql query and obtained new dbo object in my Object explorer (in Programmability->Functions)
Now I create new simple query:


select EntValue from AR.dbo.EntFunction('ABASIA');

and I get this error instead of float value of "EntValue" field after execution:
"A domain error occurred."

What is it? What I do wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 14:11:43

i think its because of this division
count(dr.DName)/@TransactionNumber AS DFreq
since both numerator & denominator are integer the result would be integer so there's a chace of you getting 0 so the below expression will cause error on such cases
sum(DFreq*LOG(DFreq))

so to avoid error try casting it to float

count(dr.DName)*1.0/@TransactionNumber AS DFreq

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources