procedure problem

[920506]

Hi, all
When I ran the following query:
select col_length('tis_select', 'surname')
it give me column length 20.
But with the following procedure

Create proc check_column_length @tabname varchar, @colname varchar
as

Select COL_LENGTH (@tabname , @colname )


exec check_column_length 'tis_select', 'surname'

It gave me NULL as result, what is the problem? I posted one similar procedure with NULL result about one month ago, similar problem, can anyone solve for me?

The reason I need this procedure is for asp code, which needs to check if the data entered by customers exceed the maxlength of the column by passing table name and column name parameter. But I am not sure if this is dorable since it has so many fields, maybe there are too many queries, don't know what is better resolution.

Betty

[rmiao]

May try with dynamic sql.

[Rawhide]

Try:

Create proc check_column_length @tabname varchar, @colname varchar
as

Select Character_Maximum_Length
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @tabname
And Column_Name = @colname

[920506]

Rawhide,
If I use
Select Character_Maximum_Length
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = 'tis_select'
And Column_Name = 'surname'
It returns 20

If I create your procedure and then use the following,

exec check_column_length 'tis_select', 'surname'

It will show 0 row affected and
with only column name Character_Maximum_Length show up,no result.

[Rawhide]

Then you are doing something wrong. It's not the proc code.

Are you sure you're running it against the correct database?

Try specifying the owner in the proc creation and in the call to it.

Create Proc dbo.check_column_length

......

exec dbo.check_column_length 'tis_select', 'surname'

[920506]

Yes, I am running against the right database, I checked stored procedures under this database. Clicked this dbo.check_column_length and then clicked on parameters, it shows three parameters:
@RETURN_VALUE(int, return value)
@tabname(varchar(1), input)
@colname(varchar(1), input)

don't know why tabname and colname become varchar(1).
When I right click on this procedure, create this script from procdure again, it looks like this:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create proc check_column_length7 @tabname varchar, @colname varchar
as

Select Character_Maximum_Length
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @tabname
And Column_Name = @colname


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Can you tell me is there anything wrong.
Betty

[Rawhide]

Try specifying a size for the varchar columns. In fact, try simply using the sysname data type.