October 13, 2006 at 2:20 pm
create proc dbo.SPTEST
@ObjectType char(4),
@ObjectName varchar(255)
Hi when we declare our params for a stored proc we give the datatype and size.. like varchar(255)..
Is there a way to avoid giving the size as if the size in db table object is changed we will have to update all the stored procedures.
Also,
I have observed that if I ignore the size and give just varchar bu default it takes (1) as the size and only the first character gets inserted/deleted.
Thanks
October 13, 2006 at 2:55 pm
Yup you'll have to update all the objects if you ever change the column size.
And yes the default size of the varchar() is 1 but it's a best pratice to always declare the size so that no one gets confused.
October 13, 2006 at 3:07 pm
You COULD declare your sproc parms to be varchar(max size) (which is 8096 in SQL 2000, or varchar(MAX) in SQL 2005), then retrieve the size from the INFORMATION_SCHEMA.COLUMNS view and enforce that size in the sproc, checking and trimming any data loaded into it and when updating the data column.
But that sounds like more work than its worth. If and when your column size changes, it is easy enought to use sp_depends to find all stored procedures that use or update that column, and see if you need to change a local variable.
Hope this helps
Mark
October 13, 2006 at 4:19 pm
Actually sp_depends is not the best way to do this because it is unreliable.
The safest way to scan for such changes is to look in the syscomments table for the column name. But that also can have some pitfalls because the text can be splitted over more than one rows so you have to code a little bit to concatenate the rows and then do the search but it's quite easy to do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply