| Author |
Topic |
|
mAdMaLuDaWg
Starting Member
42 Posts |
 Posted - 2004-08-17 : 23:11:37
|
| Hi,I've designed a constraint that uses a function that checks a view for a specific value.Now, the query that the function uses also needs to be used by my ASP.NET application. So I decided on putting the query in a stored procedure.So my question is: are there any performance impacts/benefits in using a stored procedure in a User defined function? Would it be a better idea just to use a Query in the function instead of the stored procedure?TIA.Thanks. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 09:27:09
|
| Why don't you try calling the sp from the udf - you'll soon find the answer.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2004-08-18 : 09:43:00
|
| Lol.. obviously, I can't see a difference because there isn't a huge load on my SQL Server yet.But my question was more along the lines of recommended practices. Is there anything for/against doing this? When designing functions, is this a regular practice? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 10:01:27
|
| Can you post this function please.And the SP it calls.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2004-08-18 : 10:34:31
|
| Ok, unfortunately I don't have access to the database right now, so I can't get the exact functions/procedures as I wrote them (although they are working). So there may be syntax errors as I am recalling it from memory, but hopefully you get the idea:CREATE FUNCTION CheckUniqueUser(@Username varchar(50))RETURNS intASBEGINDeclare @UserCount intexec @Usercount=sp_UniqueUsernameCount(@Username)RETURN @UsercountENDCREATE PROCEDURE sp_UniqueUsernameCountASBEGINDeclare @UserCount intSelect @UserCount=Count(*) from vwUserDB where Usename=@UserNameRETURN @UserCountEND |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 12:42:09
|
| What I was trying to get to is that you can't call an SP from a udf.That should give you an error (if you corrected the sp call - no brackets round parameter)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2004-08-18 : 13:02:14
|
| Hmm.. Thats funny because EM didn't return any validation error after I put the function in and after I put the function in a check constraint. I guess the check constraint that used the function was failing when I inserted a row and I didn't realize it.Well, thanks for the help. I really appreciate it! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 13:05:23
|
quote:
Originally posted by mAdMaLuDaWg
Hmm.. Thats funny because EM didn't return any validation error after I put the function in and after I put the function in a check constraint. I guess the check constraint that used the function was failing when I inserted a row and I didn't realize it.Well, thanks for the help. I really appreciate it!
So you are modifying code in Enterprise Manager? Not a good idea. You should use Query Analyzer instead. It is a much (much, much, much, much) better editor and will allow you to troubleshoot things easier.Tara |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2004-08-18 : 13:07:38
|
| Yeah, but EM is so much easier to work with... lol.I should start using QA more, maybe that would've caught the error. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 13:08:46
|
| It doesn't error when you add the function only when it is run.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Performance of using a stored procedure from a UDF