CODEWITHSUNDEEP
sqlsql-serverstored-procedures
aggelos
aggelos

Reputation: 67

Stored Procedure SQL Server Change column's size with data

I want to change the size of a column so I added this line in my stored procedure:

'ALTER TABLE '+ @tname+ ' ALTER COLUMN '+@fieldname+ ' nvarchar('+str(coalesce(@flength,50))+')'

But the problem is that if the column contains already data and they are bigger than the new size of the column it will raise an error. How I can check if there is data in the specific columng with size > @flength?

Upvotes: 0

Views: 1475

Answers (2)

JeffUK
JeffUK

Reputation: 4241

You can use the len() function to find any columns that are larger than the @flength:

'Select * from ' + @tname + 'where len(' + @fieldname + ')>' + coalesce(@flength,50))

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use LEN function to check the size of your data 

SELECT *
FROM   tname
WHERE  Len(fieldname) > COALESCE(@flength, 50)

Upvotes: 2

Related Questions