September 23, 2011 at 12:26 am
Hi All,
I have a SP which has input parameter field where the data type is float. I want to perform validation for this field that accepts only number (not any string nor special characters).
Can anyone help me on this ??
Thanks in Advance.....
September 23, 2011 at 2:06 am
I don't think I understand your question.
If the stored procedure accepts a float parameter, you will pass float values.
If you try to pass anything else, it will be converted explicitly or implicitly.
If you have validation issues, solve them from the app side, not in the database.
Hope this helps
Gianluca
-- Gianluca Sartori
September 23, 2011 at 6:56 am
Here is an example of what im expecting..
CREATE PROCEDURE sp_test
(
@number float
)
AS
------Do Pre-Check if valid parameter-------
SELECT * FROM Works where number =@number
GO
=========================
If i execute the above SP something like:
Exec dbo.sp_test 'sgdui'
---------------------------------------
The expected Output should be :
'Strings not allowed'
Hope you understand my requirement:-)!!
September 23, 2011 at 7:05 am
Did you look up ISNUMERIC function. Please look up in BOL. As suggested by Gianluca, I would pass a valid value from the app, validating the value in app itself.
Thanks,
Vasu
September 23, 2011 at 7:14 am
asiaindian (9/23/2011)
Did you look up ISNUMERIC function. Please look up in BOL. As suggested by Gianluca, I would pass a valid value from the app, validating the value in app itself.Thanks,
Vasu
No, don't use ISNUMERIC to validate numeric strings! It doesn't actually do what its name suggests.
Read this http://www.sqlservercentral.com/articles/IsNumeric/71512/
There's no reason why you should call a procedure passing a string value where a number is expected.
If you do it correctly from the app side, there's no way that you can pass the wrong data type.
-- Gianluca Sartori
September 23, 2011 at 7:36 am
There's no reason why you should call a procedure passing a string value where a number is expected.
If you do it correctly from the app side, there's no way that you can pass the wrong data type.
And by the time the stored proc runs the parameter has already been passed and any implicit conversion has been attempted. In other words, if you pass a value that can't be converted to the expected type it is too late. The compiler will throw an exception long before it gets inside the body.
Here is your example sproc without any validation to prove the point.
CREATE PROCEDURE sp_test
(
@number float
)
AS begin
SELECT @number
end
go
Exec dbo.sp_test 'sgdui'
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 10:49 pm
Can someone tell me how to validate the input paramater from SSRS reporting ??
September 26, 2011 at 10:55 pm
Sean, yes ,SQL shows Error conversion... Instead of this, is there any possible way to display user-defined msg??
or If the above is feasible from reporting end (SSRS)....??
September 27, 2011 at 8:09 am
thundersplash845 (9/26/2011)
Sean, yes ,SQL shows Error conversion... Instead of this, is there any possible way to display user-defined msg??or If the above is feasible from reporting end (SSRS)....??
This error is in calling the procedure with incorrect parameters. The error comes from the sql engine. I don't think there is anyway you could send a user defined message in this case. Capturing the correct parameter type should be handled in the front end.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply