Hi

I have this code:

set @sql = 'select MAX(q.eff_dt) as dw_end_dt from ' + ' (select top ' + @count_rows + '* from dbo.table where code = 99 order by eff_dt) AS q'
execute (@sql)

I need to take the value @sql and place as parameters for my other staments
set @message = ' Update dbo.table2
             Set end_dt = ''' + @sql + '''
             where eff_dt < ''' + @sql + '''
             and code = ''' + cast(@code as varchar) + ''''

Thanks

Try something like:

DECLARE @maxdt DATETIME

set @sql = 'select @maxdt = MAX(q.eff_dt) as dw_end_dt from ' + ' (select top ' + @count_rows + '* from dbo.table where code = 99 order by eff_dt) AS q'
execute (@sql)

Update dbo.table2
Set end_dt = @maxdt
where eff_dt < @maxdt
and code = cast(@code as varchar)

************

You need to understand that @sql is that *STRING* that is the command to be executed.

You certainly wouldn't expect to use that *STRING* as the value of a date!

So you need to execute the string to produce the single result you need.

And then, since that single result is now a value of the correct datatype, you can just use it in the subsequent update.

*** UNTESTED!!! ***



No reason to use EXEC to do the update.

One question I need this field with this format '+ @maxdt + ' ?

No. Definitely not.

Look, you only use the append-to-string style when you NEED to then EXEC that string.

For the UPDATE, there's no need to EXEC, so you should *NOT* be doing append-to-string.

This sentece with maxdt is not working, I'm receiving a syntax error when SQL try to convert the string to a date

DECLARE @maxdt DATETIME

set @sql = 'select @maxdt = MAX(q.eff_dt) as dw_end_dt from ' + ' (select top ' + @count_rows + '* from dbo.table where code = 99 order by eff_dt) AS q'
execute (@sql)

Update dbo.table2
Set end_dt = @maxdt
where eff_dt < @maxdt
and code = cast(@code as varchar)

If I use
set @sql = 'select MAX(q.eff_dt) as dw_end_dt from ' + ' (select top ' + @count_rows + '* from dbo.table where code = 99 order by eff_dt) AS q'
execute (@sql)

How can I access the result of the execute. Is there a system variable that contains the result or can I use another sentence ?

If nobody answers you today, I'll try to look at it at home tonight. I don't have SQL Server here, right now.

I don't understand the obsession for dynamic sql.

UPDATE t2
SET t2.end_DT = g.eff_dt
FROM Table2 AS t2
CROSS APPLY (
            SELECT eff_dt,
                ROW_NUMBER() OVER (ORDER BY eff_dt) AS recID
            FROM Table1
            WHERE code = 99
        ) AS g
WHERE g.recID = @count_rows
        AND t2.eff_dt < g.eff_dt
        AND t2.code = @code