Results 1 to 3 of 3

Thread: Dynamic sql

  1. #1
    Join Date
    Sep 2012
    Posts
    11

    Dynamic sql

    I am trying run query as follow:
    Code:
    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
    Code:
    Error convertind datatype varchar to bigint
    Where I made a mistake?

  2. #2
    Join Date
    Sep 2012
    Posts
    11
    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) + ''''

  3. #3
    Join Date
    May 2013
    Posts
    1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •