captainsina


Hi every,

I am trying to write a function in SQL server to return the minimum of a specific column value. I want to pass the name of the table as an input parameter to the function. To do that I have to user string and then EXEC to run my query :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Sina Pezeshk

-- Create date: 01/12/07

-- Description: Generates the default negative IDs for tables.

-- =============================================

ALTER FUNCTION [dbo].[getAttributeID] (@tableName varchar(255))

RETURNS int

AS

BEGIN

Declare @id int,

@command nvarchar(1000),

@params nvarchar(1000)

set @command = 'SELECT min(kiUniqueID) - 1 FROM [dbo].[' + @tableName +']'

exec sp_executesql @command, @params, @id output

return (@id)

END

Everything is good except i got this message:

Msg 557, Level 16, State 2, Line 1

Only functions and extended stored procedures can be executed from within a function.

Which means i am not allowed to call sp_executesql from withing my function.

The other method is to use EXEC instead of sp_executesql.

e.g.:

set @command = 'SELECT min(kiUniqueID) - 1 FROM [dbo].[' + @tableName +']'

EXEC @command.

The Problem here is that I don't know hoe to get a return value out of exec command. I also tried :

set @myID = EXEC @command

...................

and other possible ways of running the same query. But I cannot get the id out of exec.

So there are 2 questions here:

  1. Does anyone know how to get an output from an EXEC command
  2. Is there any better way of doing the whole process

Cheers,

Sina





Re: How to return a value using EXEC

Bushan


SQL Server will not allow executing a dynamic SQL inside a UDF. Try to convert it to SP.





Re: How to return a value using EXEC

Louis Davidson

You cannot use dynamic SQL in a function, I don't think EXEC will work in any case.

The best thing to do is take your dynamic SQL and have it create a function for each table. It is a bit more work up front, but it will be much better for performance, usage, etc.







Re: How to return a value using EXEC

captainsina

Hi,

Thats exactly what I did. Thanks a lot for your help.

Cheers