How to execute sp_unbinddefault and sp_binddefault using variable for table and column names?

Question

I am in process of changing quite a lot of tables' column type (migrating from int to float) to accommodate higher values.

Most of which columns have bindefault zero or space. Applied like so

exec sp_bindefault zero ,'table.column';

To alter the column I will have to unbind column using

exec sp_unbindefault 'table.column';

Given this has to be done for many table and a lengthy stored proc I am hoping to see if we can use variables to execute above ?

I have tried something like following, which results in errors like so,

#1

exec sp_unbindefault @table.@colunn;

or

exec sp_unbindefault [table].[column];

results in,

SQL Error [102] [S0001]: Incorrect syntax near '.'.

#2

exec sp_unbindefault '@table'+'.'+'@column';

Results in

SQL Error [102] [S0001]: Incorrect syntax near '+'.

So, is it possible to pass a values bonded to a variable when executing sp_bindefault/sp_unbindefault?

Example of stored proc

BEGIN
    declare @table varchar(200);
    declare @column varchar(200);
    declare @err nvarchar(4000);

    set @table = 'abc';
    set @column = 'xyz';

    PRINT 'table :' +  @table;
    PRINT 'column : ' + @column;

    exec sp_unbindefault '@table'+'.'+'@column';
   
   ---- other processing
   
    exec sp_bindefault zero ,'@table'+'.'+'@column';
    
END

Thanks

*"migrating from int to float"* I have to ask, is `float` also really the right data type? `bigint` would seem a better choice if you need more values, or perhaps a `decimal` with a scale of `0`. How "large" a values are you expecting to ned to store here? — Thom A, Jan 04 '23 at 12:00
Can https://stackoverflow.com/a/59476596/8339821 be of use ? — user14063792468, Jan 04 '23 at 12:01
I suggest you avoid using deprecated `sp_bindefault`. The [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-bindefault-transact-sql) cautions against it. — Dan Guzman, Jan 04 '23 at 12:16
Hi all, the changes are quite restrictive as it relates to previous changes. @Larnu the float is chosen the one :D — Milan Desai, Jan 04 '23 at 16:27

score 0 · Accepted Answer · answered Jan 04 '23 at 16:28

I was able to manage this using String parameter

BEGIN
    declare @table varchar(200);
    declare @column varchar(200);
    declare @err nvarchar(4000);
    declare @str nvarchar(max);


    set @table = 'ABC';
    set @column = 'XYZ';

    PRINT 'table :' +  @table;
    PRINT 'column : ' + @column;

    set @str = @table+'.'+@column;
    PRINT @str;
    EXEC sp_unbindefault @str; 
   
    ---- other processing

    print @str;
    EXEC sp_bindefault zero, @str; 
    
END

How to execute sp_unbinddefault and sp_binddefault using variable for table and column names?

1 Answers1