Return Table using Functions---Error

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

Transact-SQL (2000)

Return Table using Functions---Error

Author

Topic

ismail_issac
Starting Member

22 Posts

Posted - 2006-05-06 : 05:13:36

Hi

I have a function in which i want to return a table , i get a follwoing error.

Must declare the variable '@retDiffEmps'.

This is the follwoing function

CREATE FUNCTION GetEmpList (@Level int, @Location varchar(9) )
RETURNS @retDiffEmps TABLE
(
EmpID int,
SalProfile varchar(9)
)
AS
BEGIN
DECLARE @TableName varchar(50)
DECLARE @SQLString NVARCHAR(5000)

SET @TableName = 'TableName'

if(@Level = 5)
BEGIN
SET @SQLString = N'INSERT ' + @retDiffEmps +
N' SELECT Empid, SalProfile FROM ' + @TableName + N' WHERE Location =' + @Location
END
EXECUTE sp_executesql @SQLString

RETURN
END

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 05:27:36

in the Dynamic SQL string you can not use the Table Type variables so its giving you error..

What are you trying to do???

Cant you do this from Stored Procedure??

Edit: Not sure whether you can run Dynamic SQL From the functions

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-06 : 10:26:01

Yep - as chiragkhabaria says @retDiffEmps isn't available to the dynamic sql batch and you can't use dynamic sql in a function.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2006-05-08 : 03:44:29

Ismail,

if your @TableName variable is constant and you are not changing dont make it a varable but you can do something like this

INSERT @retDiffEmps
SELECT Empid, SalProfile FROM tablename WHERE Location = @Location

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources