Issues in creating dynamic SQL query

Question

I was trying to create a dynamic SQL query, But unfortunately it shows an error:

Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'select count (s.ID), count (distinct (s.ID)) from SimulationVisit SV inner join Students on s. ID=SV. Student ID Inner join Activity Context AC on AC. ID=SV. ActivityContextID WHERE (AC. TenantID = ' to data type int.

My code

declare @DateRangeStart    [DATETIME2](7), 
        @DateRangeEnd      [DATETIME2](7), 
        @TenantID          [SMALLINT] , 
        @ActivityContextID [INT], 
        @StudentID         [INT] = NULL 

'select count(s.ID),count(distinct(s.ID)) 
from SimulationVisit SV  
inner join Student s on s.ID = SV.StudentID  
inner join ActivityContext AC on AC.ID = SV.ActivityContextID 
where (AC.TenantID = ' + @TenantID + ' OR @TenantID is null)
  and (AC.ID =' + @ActivityContextID + ')
  and (SV.StudentID = ' + @StudentID + ')';

Please help me to fix this issue

Why do you create dynamic sql query for this. Use normal parameters. — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Apr 17, 2017 at 9:55

Vojtěch Dohnal · Accepted Answer · 2017-04-17 09:57:42Z

1

Do not create queries concatentating strings, it is a bad practice. Write just:

select count(s.ID),count(distinct(s.ID)) 
 from SimulationVisit SV  
 inner join Student  s 
    on s.ID=SV.StudentID  
 inner join ActivityContext AC 
    on AC.ID=SV.ActivityContextID 
 WHERE (AC.TenantID = @TenantID OR @TenantID is null)
 AND (AC.ID = @ActivityContextID)
 AND (SV.StudentID = @StudentID)

answered Apr 17, 2017 at 9:57

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail Lobanov · Accepted Answer · 2017-04-17 11:37:10Z

1

If dynamic sql is nessesary use parametrized dynamic sql query:

EXEC sp_executesql N'
SELECT COUNT(s.ID),COUNT(distinct(s.ID)) 
FROM SimulationVisit SV  
INNER JOIN Student  S ON s.ID=SV.StudentID  
INNER JOIN ActivityContext AC ON AC.ID=SV.ActivityContextID 
WHERE (AC.TenantID = @TenantID OR @TenantID is null)
AND (AC.ID = @ActivityContextID)
AND (SV.StudentID = @StudentID)',
N'  @TenantID          [SMALLINT] , 
    @ActivityContextID [INT], 
    @StudentID         [INT]',
@TenantID, @ActivityContextID, @StudentID

answered Apr 17, 2017 at 11:37

Mikhail Lobanov

3,02612 silver badges25 bronze badges

Comments

Mansoor · Accepted Answer · 2017-04-17 09:55:09Z

0

Convert INT & BIGINT value into VARCHAR datatype as below format :

   'SELECT count(s.ID),count(distinct(s.ID)) 
    FROM SimulationVisit SV 
    INNER JOIN Student  s on s.ID = SV.StudentID  
    INNER JOIN ActivityContext AC on AC.ID = SV.ActivityContextID 
    WHERE (AC.TenantID = '+ CAST( @TenantID AS VARCHAR ) + ' OR 
            @TenantID is null) AND 
          (AC.ID =' + CAST ( @ActivityContextID AS VARCHAR ) + ')
          AND (SV.StudentID = '+ CAST( @StudentID AS VARCHAR ) +')'

answered Apr 17, 2017 at 9:55

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

Comments

Sandip - Frontend Developer · Accepted Answer · 2017-04-17 09:57:07Z

Update your query as below, You have to cast variable to string/varchar value to execute dynamic query

DECLARE        
    @DateRangeStart    [DATETIME2](7), 
    @DateRangeEnd      [DATETIME2](7), 
    @TenantID          [SMALLINT] , 
    @ActivityContextID [INT], 
    @StudentID         [INT] = NULL 

DECLARE @Query VARCHAR(8000)
SET @Query='select count(s.ID),count(distinct(s.ID)) from SimulationVisit SV  inner join Student  s on s.ID=SV.StudentID  
                            inner join ActivityContext AC on AC.ID=SV.ActivityContextID 
                            WHERE (AC.TenantID = '+CAST(@TenantID AS VARCHAR(10))+' OR @TenantID is null)
                            AND (AC.ID =' +CAST(@ActivityContextID AS VARCHAR(10))+')
                            AND (SV.StudentID = '+CAST(@StudentID AS VARCHAR(10))+')'

EXEC(@Query)

Kapil · Accepted Answer · 2017-04-17 10:03:53Z

0

Since this is dynamic sql, final sql statement needs to be varchar. Convert the input parameters accordingly

  'select count(s.ID),count(distinct(s.ID)) from SimulationVisit SV  inner join Student  s on s.ID=SV.StudentID  
                        inner join ActivityContext AC on  AC.ID=SV.ActivityContextID 
                            WHERE (AC.TenantID = '+Cast(@TenantID as varchar(50))+' OR '+Cast(@TenantID as varchar(50))+' is null)
                             AND (AC.ID =' +Cast(@ActivityContextID as varchar(50))+')
                             AND (SV.StudentID = '+Cast(@StudentID as varchar(50))+')'

edited Apr 17, 2017 at 10:03

answered Apr 17, 2017 at 9:58

Kapil

1,0876 silver badges11 bronze badges

Collectives™ on Stack Overflow

Issues in creating dynamic SQL query

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related