Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems with a dynamic function Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 2:34 PM
Points: 2, Visits: 14
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
Post #1465280
Posted Wednesday, June 19, 2013 11:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
You cannot build and execute dynamic SQL within a function.

CEWII
Post #1465315
Posted Wednesday, June 19, 2013 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465332
Posted Thursday, June 20, 2013 12:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
How do you intend to use this function? Have you considered writing it as a stored procedure instead?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1465474
Posted Thursday, June 20, 2013 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 2:34 PM
Points: 2, Visits: 14
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.
Post #1465632
Posted Saturday, August 03, 2013 7:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1480754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse