Click here to Skip to main content
15,886,110 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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.
 
Share this answer
 
Comments
Uday P.Singh 25-Jul-11 7:26am    
spot on my 5!
Abhinav S 25-Jul-11 7:29am    
Thanks a ton Uday.
dhage.prashant01 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 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);

}
}
how to learn ASP.Net in home within 15days
 
Share this answer
 
Comments
Naz_Firdouse 26-Apr-13 3:57am    
you can ask this as a new thread...
why are you posting it as a solution???
Remove it....
[no name] 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900