Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Hello friends,
 
I'm trying to create dynamic SQL statement, but I'm getting following error
 
Conversion failed when converting the varchar value to data type int
 
What does this error actually means and where is this error in my StoredProcedure
 
I paste down my StoredProcedure
 
DECLARE
        @JOBTYPE INT,
        @JOBID INT,
        @SQL VARCHAR(2000),
        @CLIENTID INT,
        @BRANDCATEGORYID INT,
        @BRANDID INT
                        
        SET @JOBTYPE=2
        SET @JOBID=57032
        
        SELECT @JOBTYPE AS JOBTYPE,@JOBID AS JOBID              
        IF @JOBTYPE IN (2,5)
        BEGIN
                SELECT @CLIENTID=CAST(I.clientid AS INT),@BRANDCATEGORYID=CAST(M.brandcategoryid AS INT),
                @BRANDID=CAST(I.brandid AS INT)FROM tbl_iteration I
                INNER JOIN tbl_mechanicalbrief M ON M.mechanicalid = I.mechanicalid
                INNER JOIN tbl_client C ON C.clientid = M.clientid
                INNER JOIN tbl_brandcategory BC ON BC.brandcategoryid = M.brandcategoryid
                INNER JOIN tbl_brand B ON B.brandid = I.brandid
                WHERE I.iterationid = @JOBID
        END 
        
        SELECT @CLIENTID AS CLIENTID,@BRANDCATEGORYID AS BRANDCATEGORYID, @BRANDID AS BRANDID --THESE ARE ALL INTEGER VALUE
                        
        SET @SQL = 'select Q.noticeid clientname,brandcategoryname,brandname,comment from tbl_QCNotice Q
                                INNER JOIN tbl_client C on C.clientid = Q.clientid
                                LEFT OUTER JOIN tbl_brandcategory BC on BC.brandcategoryid = Q.brandcategoryid
                                LEFT OUTER JOIN tbl_brand B on B.brandid = Q.brandid '
        SELECT @SQL     --UPTO HERE ITS WORKING FINE
                                
        IF @CLIENTID <> 0
                SET @SQL = @SQL + ' WHERE Q.clientid IN (' + @CLIENTID + ')'
                
        SELECT @SQL     --HERE IT GIVES ERROR
        
        --IF @BRANDCATEGORYID <> 0 
        SET @SQL = @SQL + ' AND Q.brandcategoryid IN (0,'+ @BRANDCATEGORYID +')'                        
        SET @SQL = @SQL + ' AND Q.brandid IN (0,'+ @BRANDID +')'                
        SET @SQL = @SQL + ' ORDER BY noticeid DESC'     
                
        SELECT @SQL
        EXEC(@SQL) 
 
ANY IDEA HOW TO PASS VALUES TO DYNAMIC SQL??
 
THANKS IN ADVANCE
Posted 25-Jul-11 2:18am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Instead of @SQL + ' WHERE Q.clientid IN (' + @CLIENTID + ')'
you need to try @SQL + ' WHERE Q.clientid IN ( + @CLIENTID + )'.
 
Reason is simple - this is an int type of field but by using '' it is being checked against a varchar type.
  Permalink  
Comments
Uday P.Singh at 25-Jul-11 7:26am
   
spot on my 5!
Abhinav S at 25-Jul-11 7:29am
   
Thanks a ton Uday.
dhage.prashant01 at 25-Jul-11 7:40am
   
Hello that's my parameter and I'm generating dynamic query
You cant pass parameter that way
According to our suggestion i got following result
WHERE Q.clientid IN ( + @CLIENTID + )
 
I tried
@SQL = @SQL + ' WHERE Q.clientid IN (' + CAST(@CLIENTID AS VARCHAR) + ')'
 
and its working fine
Member 8299761 at 26-Apr-13 3:55am
   
Getting Conversion Failed varchar to int data type for below code:
protected void btnScr_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(ConnectionString);
string sql = "select * from employee where id=" + TextBox1.Text.Trim();
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
SqlDataReader buffer = cmd.ExecuteReader();
if (buffer.Read())
{
TextBox1.Text = buffer.GetValue(0).ToString();
TextBox2.Text = buffer.GetValue(1).ToString();
TextBox3.Text = buffer.GetValue(2).ToString();
TextBox4.Text = buffer.GetValue(3).ToString();
btnupd.Enabled = true;
btnDel.Enabled = true;
btnAdd.Enabled = false;
btnScr.Enabled = false;

}
else
{
Response.Write("<script>alert('search id doesnot exist');</script>");

}
con.Close();
}
catch(Exception error)
{
Response.Write(error.Message);
 
}
}
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

how to learn ASP.Net in home within 15days
  Permalink  
Comments
Naz_Firdouse at 26-Apr-13 3:57am
   
you can ask this as a new thread...
why are you posting it as a solution???
Remove it....
ThePhantomUpvoter at 26-Apr-13 8:05am
   
Besides not being an answer to this question, "how to learn ASP.Net in home within 15days", you can't.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Afzaal Ahmad Zeeshan 82
4 Sergey Alexandrovich Kryukov 75
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 26 Mar 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100