Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with a dynamic function


Problems with a dynamic function

Author
Message
KevinSwanner
KevinSwanner
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 27
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
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
You cannot build and execute dynamic SQL within a function.

CEWII
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16579 Visits: 17022
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)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8363 Visits: 19498
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
KevinSwanner
KevinSwanner
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 27
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search