Q:

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 @returnValue= @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

A:

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?).

