Post reply

UDF - Executing dynamic sql

  • February 14, 2006 at 9:36 am

    #171135

    Hi,

    Can I construct and execute a dynamic statement inside a user-defined function? I dont want any resultset, just the count of rows returned, like 'SELECT COUNT(*) FROM table1'. I want to take this count and do something else.

    Is this allowed in UDFs. I was not able to use GETDATE() inside the function, I didn't know everything has to be deterministic. If not, can any one suggest any other workaround.

    Thanks.

  • February 14, 2006 at 9:49 am

    #620758

    No, you cannot use dynamic sql inside of a function.

  • February 14, 2006 at 10:44 am

    #620777

    If all you want is a rowcount and you are prepared to put up with an estimate then set up your function to pass in the name of your table then do something like

    SELECT MAX(RowCnt) FROM Sysindexes where Id = Object_Id(@ParamTableName) AND IndId<2

    LinkedIn Profile

  • February 14, 2006 at 10:54 am

    #620781

    No, its not just a row count. The dynamic sql has some where conditions 'SELECT COUNT(*) FROM table1 WHERE...'

    May be I can execute the sql and pass the count as a parameter to the function. Can any one tell me how to get the scalar value that will be returned by this sql. I tried something like this, but it is throwing some error.

    DECLARE @count INT

    EXECUTE @count = ('select count(*) from table1')

    SELECT @count

    This is the error message Line 2: Incorrect syntax near '('. So I tried this

    DECLARE @count INT

    SET @count = EXECUTE ('select count(*) from table1')

    SELECT @count

    This is the error message Incorrect syntax near the keyword 'EXECUTE'

    Thanks.

  • February 14, 2006 at 1:26 pm

    #620813

    Try this:

    declare @count int

    declare @sql nvarchar(1000)

    declare @tablename nvarchar(100)

    select @tablename = 'yourtablename'

    select @sql = N'select @count = count(*) from ' + @tablename

    exec sp_executesql @sql, N'@count int output', @count output

    select @count

  • February 14, 2006 at 2:10 pm

    #620827

    Works great. Thanks.

  • February 14, 2006 at 2:11 pm

    #620828

    What you suppose to put in WHERE clause if you don't know which table you are querying?

    _____________
    Code for TallyGenerator

  • February 14, 2006 at 2:21 pm

    #620833

    The table is always the same, I know which table to query. Only the where clause changes based on some parameters.

Viewing 8 posts - 1 through 7 (of 7 total)

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