Dynamic SQL in a Function - SQL Server Forums

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)

Dynamic SQL in a Function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-14 : 08:31:34

R D Hart writes "I'm sure this has been asked somewhere before but I ahve been unable to track it down.

I am trying to create a function that will return the highest "ID" used in a table where I designate the table name throught a parameter. For example:

Select @MaxID = GetMaxID('Models')

Inside the GetMaxID function I want to execute the following query:

SELECT @ReturnValue = (SELECT MAX(ID) FROM @TableName)
-- Where @TableName is a parameter I have passed in the previous statement and @ReturnValue is obviously the value to be returned by the function.

Thanks in advance SQLTeam."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 08:56:06

not possible. You can't use Dynamic SQL in a function. Use Stored Procedure instead

marshallartist
Starting Member

3 Posts

Posted - 2007-02-14 : 10:38:08

I was afraid of that. So can I call a stored proc from a function?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 10:47:30

No.

Convert the code in the stored procedure to the function instead.
CREATE FUNCTION dbo.fnGetMaxID
(
@TableName SYSNAME
)
RETURNS INT
AS
BEGIN
DECLARE @ret INT

IF @TableName = 'TableA'
SELECT @ret = MAX(id) FROM TableA

IF @TableName = 'TableB'
SELECT @ret = MAX(id) FROM TableB

IF @TableName = 'TableC'
SELECT @ret = MAX(id) FROM TableC

-- Only if table found! Then return max value plus one. If table not found, return NULL
IF @@ROWCOUNT > 0
SELECT @ret = ISNULL(@ret, 0) + 1

RETURN @ret
END

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources