Problems with a dynamic function

Question

Post reply

Problems with a dynamic function

KevinSwanner

SSC Rookie

Points: 33
More actions
June 19, 2013 at 10:19 am

#279292

I'm not a DBA. I'm a programmer trying to do things with SQL Server. I call this a "dynamic" function, because the function is using sp_executesql for a very specific purpose: it gets a value from a database whose name is held in another table and uses that value to select records from another table. This database name is dynamic throughout our systems, and specific to a particular system.
I'm getting the error message, "Only functions and some extended stored procedures can be executed from within a function, when I call my function. I've structured it the same way as other functions. I can't see what's different between the two that would cause the error.
Function That Works
create function ref_ValidFileType(@FileType nvarchar(100)) returns bit
as begin
declare
@MasterDB nvarchar(100),
@SQL nvarchar(1000),
@IsValid bit = 0
;
set @MasterDB = dbo.fn_MasterDB();
set @SQL = N'
set @IsValid = case
when
exists(select distinct LOWER(FileType) from '+@MASTERDB+'.dbo.MimeTypes where LOWER(FileType)=LOWER(@FileType))
then 1
else 0
end
';
exec sp_executesql @SQL,N'@FileType nvarchar(100), @IsValid bit output',@FileType, @IsValid output;
return @IsValid;
end
Function That Fails
create function util_GetMasterUser() returns uniqueidentifier
as begin
declare
@MasterDB nvarchar(100),
@SQL nvarchar(1000),
@MasterUserID uniqueidentifier = null
;
set @MasterDB = dbo.fn_MasterDB();
set @SQL = N'select @MasterUserID=UserID from '+@MASTERDB+'.dbo.aspnet_Users where UserName=''masteruser@mydomain.com'';';
exec sp_executesql @SQL,N'@MasterUserID uniqueidentifier output',@MasterUserID output;
return @MasterUserID;
end
Maybe I'm simply overlooking something, but I sure don't know what it is. Can anyone tell me what I'm doing wrong here?
Thanks,
Kevin

Viewing 6 posts - 1 through 5 (of 5 total)

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

You cannot build and execute dynamic SQL within a function.

CEWII

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

Ouch, you have scalar functions calling scalar functions. This is serious performance red flag. Scalar functions are notoriously bad for performance. Even worse when they get nested.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

How do you intend to use this function? Have you considered writing it as a stored procedure instead?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

KevinSwanner SSC Rookie Points: 33 More actions · Answer 4

Thanks for everyone's input.

I know this is probably not the best way to do things. Again, I'm a programmer...not a DBA. I am not new to SQL, but I am also not a master at it. I use my programming theories/methodologies in my SQL development. So, that's why I am calling a function that calls a function that calls a function...etc.

In the interest of time and getting this to work, I have changed the function to a stored procedure. It works just as well, and probably has better performance. Performance, though, is not a top priority at this time. Sometime in the distant future, I'm sure it will be a much bigger priority.

The reason for the post was to get an explanation of why one function works and another one doesn't. I can only assume that the working function only checks the existence of data (using "exists"), while the non-working function actually selects data to be returned.

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

kevins-1001299 (6/20/2013)
Performance, though, is not a top priority at this time. Sometime in the distant future, I'm sure it will be a much bigger priority.

BWAA-HAAA!!!!! Being a DBA that spends 25% of his time cleaning up legacy code from the "old guard" on our systems at work, I can guarantee that it will be a "much bigger priority". I've seen so many people take shortcuts to supposedly save on development time and not only does it NOT save on dev time, but it costs 8 to 16 times as much to fix it and retest it later.

It doesn't take any more time to write a function (or any other code) correctly than it does to write it incorrectly. In fact, doing it right the first time may actually save on dev time because the incorrect code is frequently longer than the correct code.

Save time all the way around. Do it right and with performance in mind the first time.

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