table name as parameter

[azimmer]

I need a function that can calculate percentiles of various tables and columns. I cannot seem, however, to be able to pass table name as a parameter to the function.
Here's the code:

create function percentile (@src as varchar(64), @col as varchar(64), @perc as float) returns float
begin
declare @rowcnt int
declare @index float
declare @out float
 
select @rowcnt=count(@col) from @src
declare percentile_cursor cursor local for
    select @col from @src order by 1 asc
open percentile_cursor
set @index=cast((cast(@rowcnt as float)*perc) as int)
fetch absolute @index from percentile_cursor into @out
return @out
end
 

It won't compile (says: Server: Msg 137, Level 15, State 2, Procedure percentile, Line 8 Must declare the variable '@src'. and for Line 9 the same).

If I replace @src with any tablename, it compiles OK. Where do I go wrong?

[DonlonP]

I need a function that can calculate percentiles of various tables and columns. I cannot seem, however, to be able to pass table name as a parameter to the function.
Here's the code:

create function percentile (@src as varchar(64), @col as varchar(64), @perc as float) returns float
begin
declare @rowcnt int
declare @index float
declare @out float
 
select @rowcnt=count(@col) from @src
declare percentile_cursor cursor local for
    select @col from @src order by 1 asc
open percentile_cursor
set @index=cast((cast(@rowcnt as float)*perc) as int)
fetch absolute @index from percentile_cursor into @out
return @out
end
 

It won't compile (says: Server: Msg 137, Level 15, State 2, Procedure percentile, Line 8 Must declare the variable '@src'. and for Line 9 the same).

If I replace @src with any tablename, it compiles OK. Where do I go wrong?

You need to build your query as a string, inserting the name of your table and then execute it. I.e.

DECLARE @myQuery AS VARCHAR(200)
SET @myQuery = 'select @rowcnt=count(@ col) from ' + @src

EXEC (@myQuery)

[azimmer]

You need to build your query as a string, inserting the name of your table and then execute it. I.e.

DECLARE @myQuery AS VARCHAR(200)
SET @myQuery = 'select @rowcnt=count(@ col) from ' + @src

EXEC (@myQuery)

Thx, that's OK for the select -- any idea with the cursor? build the whole process (i.e. multiple statements) as string? i'll try tonight but isn't there a more elegant way?

[azimmer]

For what it may be worth here's the solution (non-interpolating, no error handling):

IF EXISTS (SELECT name FROM sysobjects 
      WHERE name = 'percentile' and type = 'P')
DROP PROCEDURE percentile
GO
 
create procedure percentile (@src as varchar(64), @col as varchar(64), @perc as float, @out as float OUTPUT)
as
declare @rowcnt int
declare @result float
declare @sql as nvarchar(400)
 
set @sql = 'select @rowcnt=count(*) from ' + @src
--print @sql
EXEC sp_executesql @sql, N'@rowcnt INT OUTPUT', @rowcnt OUTPUT
 
set @sql = 'declare @index int' + char(13)
    + 'declare percentile_cursor cursor local scroll for select '+@col+' from '+@src+' order by 1 asc'  + char(13)
    + 'open percentile_cursor' + char(13)
    + 'set @index=cast((1.0*cast('+cast(@rowcnt as varchar)+' as float)*cast('+cast(@perc as varchar)+' as float)) as int)' + char(13)
--    + 'print @index' + char(13)
    + 'fetch absolute @index from percentile_cursor into @result'
--print @sql
EXEC sp_executesql @sql, N'@result float OUTPUT', @result OUTPUT
 
set @out = @result
GO
 

Notes:

1. Will not work as a function only as a stored procedure
2. Uncomment comments to see debug messages
3. Ugly code, I hate it. If you have any better, please, let me know.

[Motoma]

Hey azimmer, you should turn your PM on. Let me know when you do.