SQL function for implementing count

Question

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

@Pavanred, is come up with the function that can get the count(..) , I'm looking at jimbojw.com/wiki/…, looks like it's impossible to use dynamical sql inside a UDF. — Elaine
– Elaine, Commented Dec 23, 2010 at 10:14

AdaTheDev · Accepted Answer · 2010-12-23 10:21:27Z

4

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

answered Dec 23, 2010 at 10:21

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elaine Over a year ago

error when do select dbo.ExampleSproc ('select 1') - info >> Only functions and extended stored procedures can be executed from within a function. " -- i think here's the answer -- jimbojw.com/wiki/… seems impossible to exec dynamic sql in udf?

AdaTheDev Over a year ago

@elaine Yes, that's what I said above ;)

Elaine Over a year ago

even using this way, I cannot meet the requirement. :) but thanks, I found an alternative.

StuartLC · Accepted Answer · 2010-12-23 10:31:09Z

1

Out of interest, why don't you just

select @@rowcount

after executing the query?

answered Dec 23, 2010 at 10:31

StuartLC

108k18 gold badges225 silver badges294 bronze badges

2 Comments

KM. Over a year ago

because it will always be 1 for a query like select count(*) from YourTable because there is only one row returned with the count of the entire table.

StuartLC Over a year ago

uuh ... ok, let me rephrase. Since the OP seems to want to exec an arbitrary SQL statement on a connection with the primary purpose in order to determine the rowcount from said query, why not consider @@rowcounting after executing the arbitrary statement on the same connection as an alternative.

Jan · Accepted Answer · 2010-12-23 10:18:44Z

0

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.

answered Dec 23, 2010 at 10:18

Jan

16.1k5 gold badges38 silver badges60 bronze badges

1 Comment

AdaTheDev Over a year ago

It is possible to execute dynamic sql and get the value into a variable, you just have to use sp_executesql per my answer. Obv not within a UDF

Collectives™ on Stack Overflow

SQL function for implementing count

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related