SQL Server How to validate a stored procedure parameter

3lxsmp7m 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(93)

How to validate the parameters in the stored procedure in SQL Server i.e. whether the parameter value is correct or not, and return that invalid parameter back with query,

CREATE PROCEDURE Sp_test
    (@Para1 int, 
     @para2 bigint,
     @para3 varchar)

If I pass string value to the int parameter or vice-versa, how to validate in the SQL Server side?

Or if there is a function for this to call for various parameters and datatype to check from the SQL Server side.

sql-server

来源：https://stackoverflow.com/questions/77457745/how-to-validate-a-stored-procedure-parameter

1条答案

按热度按时间

sirbozc51#

With the Below function I have tried and I got the method in success , if it returns zero the process will error with the passed parameter.

Create Function Dbo.ValidationProcess
(
@Value Varchar(250),
@DataType varchar(50)
)
RETURNS tinyint
AS
BEGIN
     DECLARE @VAR TINYINT=1
         IF(UPPER(@DATATYPE)='INT')
         BEGIN
                  SET @VAR=(SELECT CASE WHEN TRY_CONVERT(INT, @VALUE) IS NULL THEN 0  ELSE 1 END)
                  RETURN @VAR
         END
         IF(UPPER(@DATATYPE)='SMALLINT')
         BEGIN
                 SET @VAR=(SELECT CASE WHEN TRY_CONVERT(SMALLINT, @VALUE) IS NULL THEN 0  ELSE 1 END)
                  RETURN @VAR
         END
         IF(UPPER(@DATATYPE)='TINYINT')
         BEGIN
                 SET @VAR=(SELECT CASE WHEN TRY_CONVERT(TINYINT, @VALUE) IS NULL THEN 0  ELSE 1 END)
                  RETURN @VAR
         END
         IF(UPPER(@DATATYPE)='DECIMAL')
         BEGIN
                SET @VAR=(SELECT CASE WHEN TRY_CONVERT(DECIMAL, @VALUE) IS NULL THEN 0  ELSE 1 END)
                 RETURN @VAR
         END

         IF(UPPER(@DATATYPE)='BIGINT')
         BEGIN
                SET @VAR=(SELECT CASE WHEN TRY_CONVERT(BIGINT, @VALUE) IS NULL THEN 0  ELSE 1 END)
                 RETURN @VAR
         END

         IF(UPPER(@DATATYPE)='DATETIME')
         BEGIN
                SET @VAR=(SELECT CASE WHEN TRY_CONVERT(DATETIME, @VALUE) IS NULL THEN 0  ELSE 1 END)
                RETURN @VAR
         END
          RETURN @VAR

END

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to validate a stored procedure parameter

1条答案

相关问题

热门标签

最新问答