SQL: Check size of Data in Insert Operation before Inserting-CodePudding

This sounds like a trivial problem and it probably is, but I couldn't find any straightforward answer anywhere.

Basically, I have a SQL stored procedure accepting some parameters. The parameters will be processed to generate values which will be used in an insert operation later on in the stored procedure.

Now, depending on exceptional cases, the passed values may result in some insert operations to contain values exceeding max length for a column. I would like to catch those scenarios and not go through the insert operation if it's certain to fail.

Additionally, if such a scenario does occur, I want the stored procedure to terminate in a manner so the calling module knows it didn't succeed, analogical to THROW as used in exception handling in many languages.

Update: I know I can use Try/Catch but it will log error in the insights and I want to prevent this.

CodePudding user response：

So you'll want to use a try catch block in your sproc for error handling.

BEGIN TRY

    PRINT 'Put Script in this block'

END TRY
BEGIN CATCH

    THROW

END CATCH

Then to be able to tell whether or not a value will exceed the data type length you will need to get the maximum data length of your column. You can get this using below (if you have permission)

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tablename'

Lastly you will just need to get the length of your variable. You can do that like below

SELECT LEN(@VariableName)

Then you just need to put it together

CodePudding user response：

You can check value lenght come from database and column lenght.

--exec check_value_and_insert 2  
create procedure [dbo].[check_value_and_insert]  
@someinputint int  
as  
begin  
declare @get_value_frome_db varchar(max)  
declare @get_column_length int  
declare @get_value_frome_db_length int  
if @someinputint = 1  
set @get_value_frome_db ='abcdeabcdeabcdeabcdeabcdeabcde' -- these value max from column lenght and these not inserted  
if @someinputint = 2  
set @get_value_frome_db='abcde' -- these value inserted into table  
  
set @get_column_length = (select CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tbl_test_before_insert' and COLUMN_NAME='somevalues')  
set @get_value_frome_db_length = len(@get_value_frome_db)  
if @get_value_frome_db_length <= @get_column_length  
begin  
insert into tbl_test_before_insert (somevalues) values (@get_value_frome_db)  
select 'Data Inserted'  
end  
else  
select CONCAT('Not Inserted value :- ', @get_value_frome_db)  
end

if value length greater than column then, then data not insert into database and return value from store procedure. enter image description here

if value less or equal to column then, then data insert into database by store procedure.

enter image description here