CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Jinto John
Jinto John

Reputation: 363

Issues in creating dynamic SQL query

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

Upvotes: 2

Views: 149

Answers (5)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

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

Upvotes: 1

Kapil
Kapil

Reputation: 987

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))+')'

Upvotes: 0

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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)

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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)

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

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 ) +')'

Upvotes: 0

Related Questions