Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Heinz23
Yak Posting Veteran
84 Posts |
 Posted - 2008-05-19 : 04:15:18
|
| Hi all,in my SQL 2000 I have a stored procedure "AddRecord" which (obviously) adds a new record to my database. I have several input parameters. As long as I only used the SP from my own app validation was not a problem. But now also some apps from colleagues need to use the SP, and unfortunately their validations is not the best so it happens that they supply parameters just as empty strings, or values which are not allowed (e.g. in a text field you could select "ValueA" or "ValueB" but the app from my colleague supplies "ValueC" to my SP).So now I want to add validation to my SP, but i never did before and I want to ask you about best practices. Currently I do some selects like this:IF NOT EXISTS (SELECT TITLE FROM ALLTYPES WHERE TITLE = @RecordType) SET @ErrorMessage = 'Unknown RecordType -' + @RecordType + '- found. Record creation denied'This way I could validate all input parameters. In the end I have an IF-Clause checking whether @ErrorMessage is empty, otherwise the INSERT is not called.My questions:1. I've read in Books Online about 'RAISERROR'. Should I use this instead of an output parameter?2. Is it possible to create a new SP with the same name and a new output parameter @Errortext? As some apps already use 'AddRecord' they would get an error if I just add a new output parameter. Is it possible to have an overload (=2 SPs with the same name but different parameters)? If validation fails they would not get a record ID back (which is now given with SELECT RecordID FROM Records WHERE RecordID = SCOPE_IDENTITY()) so their application would know about an error even without checking errortext.Thanks for your help! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-19 : 04:23:09
|
| 1. Yes, you should use RAISERROR() and catch the error message from the front-end2.No. SPs can not be overloaded. You will have to change existing SP or create a new one with different name.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2008-05-19 : 05:03:33
|
| Hi Harsh,thanks for your fast response. Using RAISERROR would also fix the problem with the Overload because no new parameter needs to be added. I'll take a closer look at RAISERROR now. Thanks! |
|
|
|
|
|
|
Validate Input values of Stored Procedure