October 30, 2011 at 2:42 am
Dear All,
I am creating a stored procedure to insert and update the records in a table but before the insert statement i need to validate the data ( basic details such as data type and length ) and trying to race a validation message.
my table contains ( image,text,uniqueidentifier and one xml ) column's and some other char columns as well. i am validating the other char columns as below
declare @Param1 nvarchar(100)
set @param1 = ' data '
if len(@param1) <> isNull((Select max_length from sys.Columns where object_id = object_id('MyTable') and name = 'MyColumnName'),0)
begin
RAISERROR ('Invalid Data Length Please try again ..',10,1)
end
but when i am applying the same logic to image,text,uniqueidentifier and one xml columns above validation method will not work for me since sys.columns.max_length contains value 16
kindly help me to solve this problem
thanks in advance
October 30, 2011 at 2:54 am
SELECT DataLength(ImageColumn) FROM TheTable
But the max length of an image column is 2GB, as is the max size of a variable of type image, so there's probably not that much point.
p.s. Text and image are both deprecated and should not be used in any DB later than SQL 2000. Use varchar(max) and varbinary(max)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2011 at 4:48 am
Thank you very much for your prompt response
October 30, 2011 at 8:14 am
If you are using parameters on the procedure, there should be no need to validate the data length or data type.
For example:
CREATE PROCEDURE dbo.MyProcedure
@param1 int
,@param2 char(10)
,@param3 varchar(25)
,@param4 datetime
,@param5 numeric(8,2)
AS
...
None of the parameters can exceed their definition and SQL Server will raise an error to the client if you try to pass a parameter that doesn't meet the requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply