Executing dynamic SQL string within a function

Question

Executing dynamic SQL string within a function

S Niuh

SSC-Addicted

Points: 402
More actions
May 23, 2006 at 12:10 pm

#169069

I need to execute a dynamic SQL string within a function, but I get an error on the EXECUTE statement saying I cannot run the EXECUTE within a function.
Offending code as follows:
SET @strSQL = '
SELECT @intCount = ' + @DBName + '.dbo.DieLifetimeStrokeCount(2)'
EXECUTE sp_executesql @strSQL, N'@intCount int OUTPUT', @intCount OUTPUT
The reason I'm doing this is because the function needs to total the results of the above statement from several different databases, the names of which are stored in a table. So I am cursoring thru the databases and running this statement and gathering the total.
I would like this cursor to be done within the function.
Any ideas would be very appreciated!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Mike C SSC-Insane Points: 23224 More actions · Answer 1

Functions have a lot of limitations on them that will make this hard to do. Without knowing more details about what you're doing, how about using a Stored Procedure instead. I would think you could total your results in a Temp Table, and might be able to avoid the cursor in the process. This is all just guessing since you only supplied two lines of code though, but you might look into it.

George Lu-279482 SSC Veteran Points: 293 More actions · Answer 2

I have been hoping to be able to run EXEC in a function. It is so handy if can be done. But I could not. Instead I use stored procedure with OUTPUT parameter.

I feel it has some advantages in using stored proc over function. It allow you to do much more and it is easy to debug.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

Unfortunately the only use of EXEC allowed in UDF's is to call an extended stored procedure (XP). This limitation is imposed because of determinism. UDF's ideally should be deterministic; SP's are considered non-deterministic because they can potentially cause side effects to the database and return different return values for the same input values.

As an aside, XP's can also cause side effects. BOL states that if you call an XP (that can potentially have side effects on the database) from within a UDF, don't rely on consistent results.

stax68 SSChampion Points: 11711 More actions · Answer 4

How about putting this code in a trigger or sp to run when the db list is updated:

declare

@sql nvarchar(4000)

declare

@dbs table(dbname sysname)

drop

function dbo.fn_tot_alldbs

set

@sql = N'

create function dbo.fn_tot_alldbs(@yourparameter int)

returns int

as

--auto generated function code

begin

declare @intCount int

select @intcount = 0

'

--substitute your list of databases

insert

@dbs select name from sys.databases --databaselist

select

@sql = @sql + 'select @intcount = @intcount + ' + d.dbname + '.dbo.DieLifetimeStrokeCount(2)

'

from @dbs d

select

@sql = @sql + 'return @intcount

end'

print

@sql

exec

sp_executesql @sql

select

dbo.fn_tot_alldbs(0)

The output is like:

create function dbo.fn_tot_alldbs(@yourparameter int)
returns int
as
--auto generated function code
begin
declare @intCount int
select @intcount = 0
select @intcount = @intcount + master.dbo.DieLifetimeStrokeCount(2)
select @intcount = @intcount + tempdb.dbo.DieLifetimeStrokeCount(2)
select @intcount = @intcount + model.dbo.DieLifetimeStrokeCount(2)
select @intcount = @intcount + msdb.dbo.DieLifetimeStrokeCount(2)
return @intcount
end

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

S Niuh SSC-Addicted Points: 402 More actions · Answer 5

Thanks for everyone's input. It has really helped me confirm and understand the issue better. stax68, yr solution is intriguing! I would have never thought of that but it seems like it would work! Even tho I personally try to stay away from triggers because I find them difficult to work with from a maintenance standpoint (debugging, figuring out what's going on in the system, remember that they're there, etc), it would really simplify my code in general to have this function in place. I think I'll try that. Thanks!!

ps - I love this site!

stax68 SSChampion Points: 11711 More actions · Answer 6

Glad to be of help.

Re trigger phobia: If the db list is only updated by stored procs, you could put the code in there. I know what you mean about triggers, but sometimes (however much normalisation you do - 5th normal anyone?) you need them to enforce consistency rules, and I'd much rather have them in my schema than have some novice VBer maintaining them, and the web guys trying to copy what she does, etc.etc...Then there's DDL triggers in v9, which definitely are worth the work - at last, version control of database objects without cumbersome procedures! But that's another story.

A quick caveat: this solution is of course only suitable because the target table is (I assume) both very small and very infrequently updated. You wouldn't want a function to be constantly dropped and recreated (you should also ensure drop and recreate are in a transaction), and you wouldn't want the recreation process to involve any lengthy data access process.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

S Niuh SSC-Addicted Points: 402 More actions · Answer 7

S Niuh

SSC-Addicted

Points: 402

May 24, 2006 at 8:16 am

#639783

Noted - thanks!

S Niuh SSC-Addicted Points: 402 More actions · Answer 8

Well ... that almost got me there, but unfortunately the database that the 'databases' table resides on is different from where I want the function to reside. And I guess I cannot specify the database where I want the function even if I hard code the name (which I don't want to do anyways). I could possibly leave the function in the 'databases' database... I'll have to think about that. Thanks.

alter function databaseX.dbo.fn_tot_alldbsLifetimeStrokes(@DieKeyID int)

error from above stmt:

'CREATE FUNCTION' does not allow specifying the database name as a prefix to the object name.

Mike C SSC-Insane Points: 23224 More actions · Answer 9

Hmmm... Can you give more details about exactly what you're trying to do? Like where do you want this function to exist? Are you using SQL 2K or 2K5? What table are the database names stored in, and where is it located? What exactly does the DieLifetimeStrokeCount function do?

I suspect there's a pretty simple and efficient answer to what you want to do, but I'm having trouble determining exactly what you do want to do from your posts...

Robert Davis One Orange Chip Points: 28027 More actions · Answer 10

Try adding a USE statement to the dynamic SQL.

set @sql = N' USE [OtherDatabase]

create function dbo.fn_tot_alldbs(@yourparameter int)

returns int

as

--auto generated function code

begin

declare @intCount int

select @intcount = 0

'

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

stax68 SSChampion Points: 11711 More actions · Answer 11

You could put the code on the remote server (where the db list resides) and cahnge it to create a stored proc which could then be executed from yuor local server by a remote procedure call...

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

S Niuh SSC-Addicted Points: 402 More actions · Answer 12

You guys know too much. Thx for all the ideas.

End solution for me was: Use the trigger idea and move the db list table (that has the trigger) to the same database as where the function should reside. We decided that table should be in that database anyways.

It works like a charm. Thanks!

programmer 31885 Newbie Points: 3 More actions · Answer 13

It appears that if you put the name of the database in front of the call to the function, it will use the tables in that database. For example I created a function to list the columns within a table. I wanted to compare the two tables to see if there had been any changes to the column list between two databases.

select db1.dbo.fn_GetColumnList('Table1'), db2.dbo.fn_GetColumnList('Table1')

Which shows that I did indeed have two different column lists.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

Robert Davis (5/24/2006)
Try adding a USE statement to the dynamic SQL.<DT>set @sql = N' USE [OtherDatabase] <DT>create function dbo.fn_tot_alldbs(@yourparameter int) <DT>returns int <DT>as <DT>--auto generated function code <DT>begin <DT>declare @intCount int <DT>select @intcount = 0 <DT>'</DT>

I think you'll find that'll return an error about how CREATE must be the first thing in the batch.

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