Using UDF parameter in select statement of UDF

Question

Using UDF parameter in select statement of UDF

Atif

Hall of Fame

Points: 3578
More actions
July 19, 2009 at 10:48 pm

#136709

I am required to use select statement by parameter provided in UDF. Table name is provided as parameter and it is required to use in select statement in UDF. But this logic is not working
Create FUNCTION CountRows (@table nvarchar(128), @column nvarchar(128))
RETURNS bigint
as
BEGIN
declare @Count bigint
Select count(*) from @table
RETURN @Count
END
GO
Please specify how to use parameter in select statement
Regards
DBDigger Microsoft Data Platform Consultancy.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It is getting late, so what I'm going to do right now is point you in a general direction and let you run with it a bit. You are going to need to use dynamic sql, and I would use sp_executesql stored procedure to run the dynamic sql.

Start by looking up these two topics in BOL (Books Online, the SQL Server Help System). You can access it directly from SSMS by pressing the {f1} function key.

If you still have questions, please be sure to ask.

Atif Hall of Fame Points: 3578 More actions · Answer 2

Thanks for tip, I also tried this approach before but here i face limitation of UDF with following error

Msg 557, Level 16, State 2, Line 1

Only functions and extended stored procedures can be executed from within a function.

It looks that it would not be possible to use dynamic SQL in UDF.

Regards

DBDigger Microsoft Data Platform Consultancy.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

AShehzad (7/19/2009)
I am required to use select statement by parameter provided in UDF. Table name is provided as parameter and it is required to use in select statement in UDF. But this logic is not working
Create FUNCTION CountRows (@table nvarchar(128), @column nvarchar(128))
RETURNS bigint
as
BEGIN
declare @Count bigint
Select count(*) from @table
RETURN @Count
END
GO
Please specify how to use parameter in select statement
Regards

Ummmm.... no need for a UDF here... go for the gold... if you remember to do the DBCC thingy separately, you could turn the following into a view.

--_______________________________________________________________________________________________________________________

/**********************************************************************************************************************

Purpose:

Returns a single result set similar to sp_Space used for all user tables at once.

Notes:

1. May be used as a view, stored procedure, or table-valued funtion.

2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

Revision History:

Rev 00 - 22 Jan 2007 - Jeff Moden

- Initital creation for SQL Server 2000

Rev 01 - 11 Mar 2007 - Jeff Moden

- Add automatic page size determination for future compliance

Rev 02 - 05 Jan 2008 - Jeff Moden

- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name

**********************************************************************************************************************/

--===== Ensure that all row counts, etc is up to snuff

-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should

-- execute the command below prior to retrieving from the view or UDF.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more

SELECT DBName = DB_NAME(),

SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000

--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005

TableName = so.Name,

TableID = so.ID,

MinRowSize = MIN(si.MinLen),

MaxRowSize = MAX(si.XMaxLen),

ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

RowModCtr = MIN(si.RowModCtr),

HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

FROM dbo.SysObjects so,

dbo.SysIndexes si,

(--Derived table finds page size in KB according to system type

SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

FROM Master.dbo.spt_Values

WHERE Number = 1 --Identifies the primary row for the given type

AND Type = 'E' --Identifies row for system type

) pkb

WHERE si.ID = so.ID

AND si.IndID IN (0, --Table w/o Text or Image Data

1, --Table with clustered index

255) --Table w/ Text or Image Data

AND so.XType = 'U' --User Tables

AND PERMISSIONS(so.ID) 0

GROUP BY so.Name,

so.UID,

so.ID,

pkb.PageKB

ORDER BY ReservedKB DESC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Atif Hall of Fame Points: 3578 More actions · Answer 4

Thanks for reply, Moden. Actually this example UDF was to convey my problem. I was looking to create some UDFs for various tasks and was required to use dynamic sql in UDF. I think i have to redesign my approach as it looks impossible to use dynamic sql in UDF.

Regards

DBDigger Microsoft Data Platform Consultancy.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

AShehzad (7/20/2009)
Thanks for reply, Moden. Actually this example UDF was to convey my problem. I was looking to create some UDFs for various tasks and was required to use dynamic sql in UDF. I think i have to redesign my approach as it looks impossible to use dynamic sql in UDF.
Regards

You can still use the UDF to query SysIndexes or the equivalent new system view. You just have to remember to do the DBCC thing on the given table before you call the UDF.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Ummmm.... now that I think about it, there's some new (new in 2k5) system view that actually gives you the correct number of rows for any given table without having to do the DBCC call. I just can't remember the name of the view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

This is one of the DMV's that you could use. You'll need to figure out exactly how at the moment as I need to head out to work.

sys.dm_db_partition_stats

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

That's the one, Lynn... it has a RowCount for each object ID and, I believe, that row count is always up to snuff.

If you read BOL about the system view, you'll find that you need the sum of the rowcounts with where the index ID = 1 or 0 (both will not occur at the same time) because you could have more than one partition for the object (table or indexed view).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)