Post reply

EXEC in SQL Functions

  • September 1, 2006 at 2:41 am

    #367303

    I need to pass a table name and id to a function and return a row count

    I need to use EXEC or SP_EXECUTESQL to run dynamic SQL

    It wont work in functions. Following is my function

    alter FUNCTION [dbo].[GetRowCount] (@TblName NVARCHAR(25) , @Itemid INT)

    RETURNS INT

    AS BEGIN

    DECLARE @RowCnt INT

    set @RowCnt = 0

    DECLARE @Sqlstring nvarchar(2000)

    set @Sqlstring = 'SELECT @RowCnt = COUNT(*) FROM ['+ @TblName +'] WHERE Itemid = '+ convert(varchar(10),@Itemid)

    EXEC @Sqlstring

    RETURN @RowCnt

    END

    while executing this I get the following error ....

    "Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

    does anyone have any ideas of this ?

    Thanks.

    vidhya

  • September 1, 2006 at 4:28 am

    #657977

    Vidhya

    Can you not use a stored procedure instead of a function?

    John

  • September 1, 2006 at 5:10 am

    #657979

    Hi John,

    My requirement is like that. I need that functionality inside Function. If possible pls tell me to proceed further.

    Vidhya

  • September 1, 2006 at 6:40 am

    #658008

    You should read BOL to understand the limitations and uses of various objects in SQL Server. You might be able to do this through a dot net function although I'd advise against it.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • September 2, 2006 at 11:53 am

    #658179

    You cannot do it using functions in sql server.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply