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
 
<pre>
    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 &lt;&gt; 0
            SET @SQL = @SQL + ' WHERE Q.clientid IN (' + @CLIENTID + ')'
 
    SELECT @SQL     --HERE IT GIVES ERROR

    --IF @BRANDCATEGORYID &lt;&gt; 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) </pre>

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 490
1 Sergey Alexandrovich Kryukov 335
2 ProgramFOX 265
3 Maciej Los 245
4 Andreas Gieriet 200
0 OriginalGriff 465
1 Sergey Alexandrovich Kryukov 275
2 ProgramFOX 265
3 Maciej Los 245
4 Andreas Gieriet 200


Advertise | Privacy | Mobile
Web03 | 2.8.150331.1 | Last Updated 26 Mar 2014
Copyright © CodeProject, 1999-2015
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