Identify INT,BIGINT,character in stored proc

Question

Identify INT,BIGINT,character in stored proc

thbaig

Hall of Fame

Points: 3003
More actions
November 3, 2021 at 6:45 pm
Go to Answer
#3947633

Hi,
I have a stored proc that takes comma separated list , split it and use it to match against table columns.
e.g.
execute spValidationGet '821780031254,821780031250,1473983,1473903,65644'
I need to implement some input validation in it
1- I should discard character string e.g. 821780031254,821780031250,1473983,abc,1122' , my proc should remove abc before comparing it to INT column
2- I should remove any input that exceeds INT limit , e.g. 821780031254,821780031250 , my proc should remove them before comparison to avoid overflow error.

or it is easy to say , my proc should pick INT values from input and proceed.
Is it possible in stored proc ? I know I can use IsNumeric to identify INT but it will not work on INT vs BigINt scenario
ScottPletcher

SSC Guru

Points: 101097
More actions
November 3, 2021 at 8:02 pm
Answer
#3947665

Which version of SQL Server? If you're in a recent enough version, you can use function TRY_CAST to see if the value is a valid int or not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

thbaig wrote:

Hi,
I have a stored proc that takes comma separated list , split it and use it to match against table columns.
e.g.
execute spValidationGet '821780031254,821780031250,1473983,1473903,65644'
I need to implement some input validation in it
1- I should discard character string e.g. 821780031254,821780031250,1473983,abc,1122' , my proc should remove abc before comparing it to INT column
2- I should remove any input that exceeds INT limit , e.g. 821780031254,821780031250 , my proc should remove them before comparison to avoid overflow error.
or it is easy to say , my proc should pick INT values from input and proceed.
Is it possible in stored proc ? I know I can use IsNumeric to identify INT but it will not work on INT vs BigINt scenario

Actually, all of this is a REALLY bad idea. You shouldn't be removing inputs and then running with the good ones that remain. You should be rejecting the entire run.

There is, of course, always an exception to the rule so, let me ask, what is the proc doing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thbaig Hall of Fame Points: 3003 More actions · Answer 2

thbaig

Hall of Fame

Points: 3003

November 4, 2021 at 10:20 am

#3947830

Thank you. TRY_Convert exist in my version

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

thbaig wrote:

Thank you. TRY_Convert exist in my version

Now that you have your answer, would you please tell us what your stored procedure is doing? I'm really interested in why bad inputs can simply be discarded and ignored.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thbaig Hall of Fame Points: 3003 More actions · Answer 4

sure sir.

following is my proc code. proc is taking comma separated input string . it get parse and then compare to db table. if product(s) are invalid , i sent list of those invalid one. as this proc is part of Webservice call and customer are sending data with no checks. in current case they sent some character values and some BigInt that our system was not able to handle. So I just applied try_convert identify required data type. now I am not discarding wrong data , but sending it back in output.

ALTER PROCEDURE [dbo].[spValidationGet]
@style VARCHAR(MAX) 
AS
BEGIN

--==========================================================
-- Declare
--==========================================================
DECLARE @SkuList TABLE (
Sku VARCHAR(MAX) 
,iKey INT
,sIndex INT
,TypeIdentify int 
)
--===========================================================
-- Split Input string
--===========================================================

INSERT INTO @SkuList 
SELECT item, try_convert(INT,left(item,len(item)-1)),try_convert(int,right(item,1)),try_convert(INT,item)
FROM dbo.fnSplit(@style, ',');

--=================================================================
-- Final Output
--=================================================================
;WITH mySku AS
( 
SELECT inv.iKey,
Inv.sIndex,
Sku = CONVERT(VARCHAR,inv.iKey)+CONVERT(VARCHAR,inv.sIndex) 
FROM dbo.tblProduct inv
)

SELECT sku.Sku
FROM mySku
RIGHT JOIN @SkuList sku
ON sku.iKey = mySku.iKey
AND sku.sIndex = mySku.sIndex
WHERE mySku.sku IS NULL
AND TypeIdentify is not null
UNION 
SELECT SKU
FROM @SkuList
WHERE TypeIdentify IS NULL;
END?

Jeff Moden wrote:

thbaig wrote:
Thank you. TRY_Convert exist in my version
Now that you have your answer, would you please tell us what your stored procedure is doing? I'm really interested in why bad inputs can simply be discarded and ignored.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

thbaig wrote:

now I am not discarding wrong data , but sending it back in output.

Ah... that's the ticket. Thanks for taking the time to explain. I was really concerned about what you were doing. That one statement explains it all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)