help with dynamic sql within functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with dynamic sql within functions

Author

Topic

kevindockerty
Starting Member

27 Posts

Posted - 2006-09-05 : 16:32:48

Hi all
Basically I am trying to pass a table name to a function and return a row count.
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions - is there a work around

alter FUNCTION [dbo].[GetRowCount] (@TblName NVARCHAR(25) )
RETURNS INT
AS BEGIN
DECLARE @RowCnt INT
DECLARE @Sqlstring nvarchar(2000)

set @Sqlstring = 'SELECT @RowCnt = COUNT(*) FROM '+ @TblName
EXEC @Sqlstring

RETURN @RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function."

I know I can do this in a stored procedure - but its not a suitable solution for lots of technical reasons

Does anyone know of a workaround ?
I'm told 'openquery' is an option but I cant get this to work

any help much appreciated
KD

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-05 : 16:59:37

I'm sure there is a better (big picture) way than a function that returns row count of whatever tablename you pass but here is one possible way to accomplish that. This assumes your tables have primary keys and may not be entirely accurate during high moments of contention:


use pubs
if object_id('dbo.fnJunk') > 0 drop function dbo.fnjunk
go
create function dbo.fnJunk (@tbName varchar(50))
returns int
as
begin
	declare @rc int
	select @rc = rows 
	from sysindexes 
	where object_name(id) = @tbName
	and indid < 2

	return @rc
end
go
select	dbo.fnJunk ('authors') AuthorsCount
	,dbo.fnJunk ('publishers') publishersCount

EDIT:
if you meant is there a way to EXEC dynamic sql from within a function, no there is not (not that I know of).

Be One with the Optimizer
TG

kevindockerty
Starting Member

27 Posts

Posted - 2006-09-05 : 17:29:50

thanks for that

re: you comment
"if you meant is there a way to EXEC dynamic sql from within a function, no there is not (not that I know of)."

yes, that was basically my question - I simplified it by using the table count as an example - and I'm fairly sure your solution to that would work
many apologies if I wasted your time.

what I actually want to do - just in case your interested - is perform numerous processes on a number of databases ( about 200 of them ) that all sit on one sql server instance.

they are all of the exactly the same format ( same tables, views, procs, triggers etc ) and named basically 'mapdb1', 'mapbd2', 'mapdb3' ...etc

the problem we have is running code or updates etc against each database.
ie if we change a table - we have to apply this change across 200 databases - if we change a stored proc - we have to do it 200 times across each DB !!

We can write code using stored procs and cursor through them but its a real pain in the a***

dont ask why the set up is like this - I just inherited it

cheers and thx again
KD

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-05 : 21:01:08

Note that to get proper rowcount from sysindexes you need to run DBCC Updateusage

Madhivanan

Failing to plan is Planning to fail

soMuchToLearn
Starting Member

3 Posts

Posted - 2008-05-16 : 16:20:50

what I actually want to do - just in case your interested - is perform numerous processes on a number of databases ( about 200 of them ) that all sit on one sql server instance.

the problem we have is running code or updates etc against each database.
ie if we change a table - we have to apply this change across 200 databases - if we change a stored proc - we have to do it 200 times across each DB !!

We can write code using stored procs and cursor through them but its a real pain in the a***

dont ask why the set up is like this - I just inherited it
KD
[/quote]

Hi KD,
I have a very similar situation of so many carbon-copied databases which need maintenance during development. While it does take longer in the beginning, using cursors has been a life saver for making a chance across all the databases. I use a cursor to select all the appropriate databases, then set an nvarchar string to begin use ['+@dbname+'] and then the command(s) I want to run in each database. I tend to print out each database name so I have something watch while it runs.

Getting back to your post, I can do all sorts of alter tables, create tables and the like. I get stuck trying to do an alter procedure, alter function or a create of either one. The error reads, "must be the first statement in a batch". Just wondering if you've gotten a way to loop through your databases and if that syntax / concept may work-around this limitation for batching development changes.

Cheers,
Brent in the US

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-16 : 17:21:12

check this out, maybe it will point you in the right direction.

EXEC sp_MSforeachdb 'select top 10 * from information_schema.tables'

An infinite universe is the ultimate cartesian product.

soMuchToLearn
Starting Member

3 Posts

Posted - 2008-05-16 : 21:56:15

quote:
Originally posted by cat_jesus

check this out, maybe it will point you in the right direction.

EXEC sp_MSforeachdb 'select top 10 * from information_schema.tables'

Sure did! Here's a page explaining how to use it to do exactly what I was out to do: create a stored procedure in each db. [url]http://www.mssqltips.com/tip.asp?tip=1414[/url]

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-17 : 08:39:45

quote:
Originally posted by cat_jesus

check this out, maybe it will point you in the right direction.

EXEC sp_MSforeachdb 'select top 10 * from information_schema.tables'

An infinite universe is the ultimate cartesian product.

soMuchToLearn
Starting Member

3 Posts

Posted - 2008-05-17 : 10:40:59

Note that sp_MSforeachdb is undocumented
You can however simulate it
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan
[/quote]

Hi Madhivanan,
I checked out the link. The concepts there were similar to what I was using. They work great for commands that aren't creating/altering stored procs or functions. The catch-22 with create/alter stored proc/function is that database prefixes can't be used (ie create function [databasename1].dbo.newFunction) and the create function line must be first in a batch eliminating a preceding use [databasename1]. The additional catch with exec() and even sp_executeSQL is that they run in their own thread which doesn't communicate with the caller. So an EXEC('use [databasenam1]') followed by an EXEC('create function abc') will create a function in database the script is executed from. Otherwise, the ideas on the website would appear to work fine. Open to suggestions / alternatives / corrections.

jmvspam
Starting Member

2 Posts

Posted - 2009-07-10 : 11:59:43

Hello everybody,
I recently had a similar issue. In fact the error message is not properly formatted since sp_executesql is an extended stored procedure as you can check by the following script:
select objectproperty(object_id('sp_executesql'),'IsExtendedProc')
returns
-----------
1
Since we can’t use sp_executesql even it’s a XP, I had to found another workaround by using sp_OAMethod: My scenario was: how to find dynamically the number of rows in a table according some criteria (null values in my scenario). Using sp_OAMethod I built the following function:
--------------------------------
IF object_id(N'dbo.fc_ContaRegistros_x_Criterio') is not null DROP FUNCTION [dbo].[fc_ContaRegistros_x_Criterio]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fc_ContaRegistros_x_Criterio (
@str_TBName VARCHAR(100),
@str_Criter VARCHAR(500))
RETURNS BIGINT
AS
BEGIN
-- Objetivo : Contar numero de registros de uma determinada tabela de acordo com o critério passado
-- Criação : Josué Monteiro Viana - 09/07/09
/*
Exemplo:
DECLARE @count INT
SET @count = dbo.fc_ContaRegistros_x_Criterio('master.dbo.sysobjects', '')
PRINT @count
SET @count = dbo.fc_ContaRegistros_x_Criterio('crk.dbo.acao', 'where cod_acao is null')
PRINT @count
*/
DECLARE
@int_objSQL INT,
@int_erros INT,
@int_objSelectCountResult INT,
@bint_SelectCount BIGINT,
@sql NVARCHAR(2000)

EXEC @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT
EXEC @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE
EXEC @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
--SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' WHERE ' + @str_Criter
SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' ' + @str_Criter
SET @sql = 'ExecuteWithResults("' + @sql + '")'
EXEC @int_erros = sp_OAMethod @int_objSQL, @sql, @int_objSelectCountResult OUTPUT
EXEC @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT
EXEC @int_erros = sp_OADestroy @int_objSQL
-- debug info: not valid inside a fc
--if @int_erros <> 0 EXEC sp_OAGetErrorInfo @int_objSQL else print 'ok'
if @int_erros <> 0 SET @bint_SelectCount = @int_erros
RETURN @bint_SelectCount
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------

I’m think my UDF can help in what the fisrt post asked for.

Best wishes,
Josue Monteiro Viana

Josué Monteiro Viana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources