SQL execute return of select

I have select

select 'alter table '+so.name+
' drop '+sdc.name+' go sp_bindefault ''abc'' ,'''+so.name+'.'+sc.name+''''
from sys.objects as so
       join sys.columns as sc on so.object_id=sc.object_id
       join sys.default_constraints as sdc on sc.object_id=sdc.parent_object_id
                                                      and sc.column_id=sdc.parent_column_id
where so.type='U'
        and so.name like 'z%'
        and sdc.definition='(getdate())'

Im droping getdate defaults and want bind one getdate default. Result of this has 2 lines

alter table zamestnanci drop DF__zamestnan__datum__2E1BDC42 go sp_bindefault 'abc' ,'zamestnanci.datum_pridania'
alter table zamestnanci2 drop DF__zamestnan__datum__2F10007B go sp_bindefault 'abc' ,'zamestnanci2.datum_pridania'

and i want to execute it in one command. Any ideas?

Answer

You won’t be able to dynamically generate the SQL and execute it in a single set based query. You’ll have to iterate through the results of your select – either in a CURSOR or WHILE loop – and build the generated statement into a variable and then execute that with EXEC or sp_executesql; e.g.

    DECLARE @TableName sysname,
        @ConstraintName sysname,
        @ColumnName sysname,
        @sql nvarchar(4000);

DECLARE getdateDefaults CURSOR FAST_FORWARD AS
        SELECT so.name AS TableName,
               sdc.name AS ConstraintName,
               sc.name AS ColumnName
        from sys.objects as so
               join sys.columns as sc on so.object_id=sc.object_id
               join sys.default_constraints as sdc on sc.object_id=sdc.parent_object_id
                                                              and sc.column_id=sdc.parent_column_id
        where so.type='U'
                and so.name like 'z%'
                and sdc.definition='(getdate())';

OPEN getdateDefaults;
FETCH NEXT FROM getdateDefaults INTO @TableName, @ConstraintName, @ColumnName;
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @sql = N'alter table '+@TableName+N' drop '+@ConstraintName+N';'
    EXEC sys.sp_executesql  @stmt = @sql;
    SET @sql = N' sp_bindefault ''abc'' ,'''+@TableName+'.'+@ColumnName+N''';'
    EXEC sys.sp_executesql  @stmt = @sql;

    FETCH NEXT FROM getdateDefaults INTO @TableName, @ConstraintName, @ColumnName;
END
CLOSE getdateDefaults;
DEALLOCATE getdateDefaults;