SQL function for implementing count

I'm using SQL Server 2005, and want a user defined function to implement the following:

Parameter: @SQL varchar(max)

execute 'select count(1) from (' + @sql + ')'

and return the result as integer.

This is the dummy code >>

ALTER FUNCTION [dbo].udf_GetCountFromSQL ( @SQL VARCHAR(MAX) )
RETURNS INT
AS 
    BEGIN
        DECLARE @returnValue INT
        SET @SQL = 'SELECT COUNT(1) FROM (' + @SQL + ')'
        EXEC @re开发者_高级运维turnValue= @SQL

        RETURN @returnValue ;
    END

@SQL here is dynamic sql, not stored procedure name, so it doesn't work.

Wait to be helped out, thanks in advance.

Elaine

You can't execute dynamic SQL in a user defined function. You'd need to change it to a stored procedure and use sp_executesql to get the count into a variable - you can then pass the count out as an OUTPUT parameter (or RETURN value if you want).

CREATE PROCEDURE dbo.ExampleSproc(@SQL NVARCHAR(MAX), @RowCount INTEGER OUTPUT)
AS
BEGIN
    SET @SQL = 'SELECT @RowCount = COUNT(1) FROM (' + @SQL + ') x'
    EXECUTE sp_executesql @SQL, N'@RowCount INTEGER OUT', @RowCount OUT
END

Then call like this:

DECLARE @RowCount INTEGER
EXECUTE dbo.ExampleSproc '...Some SQL...', @RowCount OUTPUT

However, you need to be very careful as you're opening yourself up to SQL injection attacks (what if @SQL contains something dodgy?).

Out of interest, why don't you just

select @@rowcount

after executing the query?

To call dynamic SQL you have to use parantheses around the parameter:

EXEC (@SQL)

But you can't use a statement with side effects inside a UDF, so its impossible to execute dynamic SQL.

Besides this, its not possible to return a value from your dynamic SQL query and store it in a variable.