Stored Procedures - Validating Variables
-
Hi Everyone. I'm finding it hard to get information of verifying variables passed into a stored procedure. Specifically if the value is NULL then I want to abort the procedure and let VB.NET know the insert failed without issuing an exception. I found some information that gave me the following IF statement but the layout is not like VB so I'm not sure where to go from here... IF (@RateLevel1 IS NOT NULL) OR (@RateLevel2 IS NOT NULL) NOTE: I borrowed the following code from a tutorial video so if you find any issues please let me know. ALTER PROCEDURE dbo.sptblBillingRatesInsert ( @Description nvarchar(150), @RateLevel1 money, @RateLevel2 money, @RateLevel3 money, @RateLevel4 money, @RateLevel5 money, @BillingLevelID int OUTPUT, @ModifiedStamp timestamp OUTPUT ) AS SET NOCOUNT ON; -- Inserts a row in Billing Rates INSERT INTO tblBillingRates (Description, RateLevel1, RateLevel2, RateLevel3, RateLevel4, RateLevel5) VALUES (@Description, @RateLevel1, @RateLevel2, @RateLevel3, @RateLevel4, @RateLevel5); -- Checks if the last statement produced an error IF @@ROWCOUNT>0 AND @@ERROR=0 SELECT @BillingLevelID = BillingLevelID, @ModifiedStamp = ModifiedStamp FROM tblBillingRates WHERE (BillingLevelID = SCOPE_IDENTITY());
-
Hi Everyone. I'm finding it hard to get information of verifying variables passed into a stored procedure. Specifically if the value is NULL then I want to abort the procedure and let VB.NET know the insert failed without issuing an exception. I found some information that gave me the following IF statement but the layout is not like VB so I'm not sure where to go from here... IF (@RateLevel1 IS NOT NULL) OR (@RateLevel2 IS NOT NULL) NOTE: I borrowed the following code from a tutorial video so if you find any issues please let me know. ALTER PROCEDURE dbo.sptblBillingRatesInsert ( @Description nvarchar(150), @RateLevel1 money, @RateLevel2 money, @RateLevel3 money, @RateLevel4 money, @RateLevel5 money, @BillingLevelID int OUTPUT, @ModifiedStamp timestamp OUTPUT ) AS SET NOCOUNT ON; -- Inserts a row in Billing Rates INSERT INTO tblBillingRates (Description, RateLevel1, RateLevel2, RateLevel3, RateLevel4, RateLevel5) VALUES (@Description, @RateLevel1, @RateLevel2, @RateLevel3, @RateLevel4, @RateLevel5); -- Checks if the last statement produced an error IF @@ROWCOUNT>0 AND @@ERROR=0 SELECT @BillingLevelID = BillingLevelID, @ModifiedStamp = ModifiedStamp FROM tblBillingRates WHERE (BillingLevelID = SCOPE_IDENTITY());
-
Hi Everyone. I'm finding it hard to get information of verifying variables passed into a stored procedure. Specifically if the value is NULL then I want to abort the procedure and let VB.NET know the insert failed without issuing an exception. I found some information that gave me the following IF statement but the layout is not like VB so I'm not sure where to go from here... IF (@RateLevel1 IS NOT NULL) OR (@RateLevel2 IS NOT NULL) NOTE: I borrowed the following code from a tutorial video so if you find any issues please let me know. ALTER PROCEDURE dbo.sptblBillingRatesInsert ( @Description nvarchar(150), @RateLevel1 money, @RateLevel2 money, @RateLevel3 money, @RateLevel4 money, @RateLevel5 money, @BillingLevelID int OUTPUT, @ModifiedStamp timestamp OUTPUT ) AS SET NOCOUNT ON; -- Inserts a row in Billing Rates INSERT INTO tblBillingRates (Description, RateLevel1, RateLevel2, RateLevel3, RateLevel4, RateLevel5) VALUES (@Description, @RateLevel1, @RateLevel2, @RateLevel3, @RateLevel4, @RateLevel5); -- Checks if the last statement produced an error IF @@ROWCOUNT>0 AND @@ERROR=0 SELECT @BillingLevelID = BillingLevelID, @ModifiedStamp = ModifiedStamp FROM tblBillingRates WHERE (BillingLevelID = SCOPE_IDENTITY());
Why do you want to do this? If you have designed your system properly, then you should have code in place at a much higher level to prevent this from happening. It is much better to warn the user that there is a problem and give them a chance to do something about it before you attempt the insert, which would just be an unnecessary trip to the database. In general, it is much better to validate input as close to the user as possible.
Deja View - the feeling that you've seen this post before.
-
Why do you want to do this? If you have designed your system properly, then you should have code in place at a much higher level to prevent this from happening. It is much better to warn the user that there is a problem and give them a chance to do something about it before you attempt the insert, which would just be an unnecessary trip to the database. In general, it is much better to validate input as close to the user as possible.
Deja View - the feeling that you've seen this post before.
Hi Pete, I was watching the Microsoft VB training videos by Beth Massi. She showed two things: - using stored procedures server side to make the app more secure - using VB code on the dataset to check data and issue error providor status for that column. These items are supposed to keep the "business logic" away from the forms and in a central location rather than re-keying in different forms. Well I put it into practice and it works nicely, BUT ONLY IF you type the right things in....but as we all know users rarely do as they should. I can break the system easily if I tab through all fields in the datagrid. The error provider flags them as needing attention (NULL ETC) but once you reach that last field it causes an unhandled exception. My instinct is to handle the excpetion on the form using an event like leavecell or dataerror (not sure on names sorry) BUT then I'm splitting my logic between the dataset, stored procedure and the form....and breaking the rules of centralising.... Hence I thought maybe I should look to do it in the stored procedure....but as you say that means the user doesn't get a mice message and warning......so confused
-
Hi Pete, I was watching the Microsoft VB training videos by Beth Massi. She showed two things: - using stored procedures server side to make the app more secure - using VB code on the dataset to check data and issue error providor status for that column. These items are supposed to keep the "business logic" away from the forms and in a central location rather than re-keying in different forms. Well I put it into practice and it works nicely, BUT ONLY IF you type the right things in....but as we all know users rarely do as they should. I can break the system easily if I tab through all fields in the datagrid. The error provider flags them as needing attention (NULL ETC) but once you reach that last field it causes an unhandled exception. My instinct is to handle the excpetion on the form using an event like leavecell or dataerror (not sure on names sorry) BUT then I'm splitting my logic between the dataset, stored procedure and the form....and breaking the rules of centralising.... Hence I thought maybe I should look to do it in the stored procedure....but as you say that means the user doesn't get a mice message and warning......so confused
A "typical" design would be to have a user interface where you would identify which elements of the data absolutely had to be present and validate that they were there before you could do anything else. Once the data has been validated, it is then passed to a business layer. Again, the business layer would be responsible for validating the data. Finally, the data would be passed over to the data layer.
Deja View - the feeling that you've seen this post before.
-
Why do you want to do this? If you have designed your system properly, then you should have code in place at a much higher level to prevent this from happening. It is much better to warn the user that there is a problem and give them a chance to do something about it before you attempt the insert, which would just be an unnecessary trip to the database. In general, it is much better to validate input as close to the user as possible.
Deja View - the feeling that you've seen this post before.
I validate at both locations. This is especially necessary when you code in a team. The DBA is responsible for making sure, to the best of his/her ability, that garbage is not put into the system and that the user/developer knows why there is a rejection of input. The front end developer should make sure that they limit the amount of rejections to the best of their ability. I find that checking inputs on both sides is highly beneficial and reduces DB errors dramatically. The only time I stray from this is when the data needs to be verified against other existing data in the database. Here I make the DBA sole responsibility so that verfication data does not need to go back and forth across the wire.
-
Hi Everyone. I'm finding it hard to get information of verifying variables passed into a stored procedure. Specifically if the value is NULL then I want to abort the procedure and let VB.NET know the insert failed without issuing an exception. I found some information that gave me the following IF statement but the layout is not like VB so I'm not sure where to go from here... IF (@RateLevel1 IS NOT NULL) OR (@RateLevel2 IS NOT NULL) NOTE: I borrowed the following code from a tutorial video so if you find any issues please let me know. ALTER PROCEDURE dbo.sptblBillingRatesInsert ( @Description nvarchar(150), @RateLevel1 money, @RateLevel2 money, @RateLevel3 money, @RateLevel4 money, @RateLevel5 money, @BillingLevelID int OUTPUT, @ModifiedStamp timestamp OUTPUT ) AS SET NOCOUNT ON; -- Inserts a row in Billing Rates INSERT INTO tblBillingRates (Description, RateLevel1, RateLevel2, RateLevel3, RateLevel4, RateLevel5) VALUES (@Description, @RateLevel1, @RateLevel2, @RateLevel3, @RateLevel4, @RateLevel5); -- Checks if the last statement produced an error IF @@ROWCOUNT>0 AND @@ERROR=0 SELECT @BillingLevelID = BillingLevelID, @ModifiedStamp = ModifiedStamp FROM tblBillingRates WHERE (BillingLevelID = SCOPE_IDENTITY());
The way you have it does not allow nulls so you may just want to handle the exception msg 201 as a null parameter. The other way is to add a default @RateLevel1 money = NULL and have an output parameter @IsNull of type bit and set to true if any parameters are null.