Dynamic sql

[ironcurtain]

I am trying run query as follow:

declare @report_id int
set @report_id = 309
declare @amount_events bigint
declare @reports_view_name sysname
declare @count_activites_command nvarchar (2000)
declare @last_generation datetime
 
set @reports_view_name = (select reports_view_name from reports_generation_view where report_id =@report_id)
set @last_generation = (select last_generation from reports where report_id = @report_id)

set @count_activites_command = 'select ' +@amount_events + '= count(*) from Audit.dbo.' + @reports_view_name + ' where import_date > ' +@last_generation

exec sp_executesql @count_activites_command, N'@amount_events bigint output', @amount_events out
select @amount_events

But I got error

Error convertind datatype varchar to bigint

Where I made a mistake?

[ironcurtain]

I figured it out how it should looks like:

set @count_activites_command = N'select @amount_events = count(*) from Audit.dbo.' + @reports_view_name + ' where import_date > ''' + convert(varchar, @last_generation) + ''''

[martin]

It will be interesting to know if the solution mentioned here could help. Because I was in such a situation, couldn't find a solution and tried to re-build my query in an another way - as a result, it was very cumbersome.