update row with SQL statement with LIKE '%' fails

Hi Kev,

SQL statement is stored perfectly in the table:

SELECT * FROM LabelReport WHERE ReportID = @Var1 AND CompanyIdList LIKE '%' + @Var2 + '%'

Unfortunately when I use it in my program I get:

{"Incorrect syntax near the keyword 'LIKE'."}.

If I execute the stored query in the query window it works perfectly.

I'm totally lost now...

Best regards,

Mikko

how are you executing the sql from your 'program'?

Here is the executor, it works if I have only Vars without LIKE:

    private DataSet QueryWithMaxTwoVars(string sConnStr, string sTableName, string sQry, string Var1, string Var2)
{

Some stuff omitted to keep under xxChars!!!

         // Create SQL query with variables
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.CommandText = sQry;
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);



        // Define parameters used in query object
        if (Var1 != "")
        {
            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@Var1";
            param1.Value = Var1;
            mySqlCommand.Parameters.Add(param1);
        }

        if (Var2 != "")
        {
            SqlParameter param2 = new SqlParameter();
            param2.ParameterName = "@Var2";
            param2.Value = Var2;
            mySqlCommand.Parameters.Add(param2);
        }

        mySqlDataAdapter.Fill(dsCollectedData, sTableName);

        mySqlConnection.Close();

        return dsCollectedData;
}

Ahh over to someone who can read that then .....I'm not a developer, just a SQL guy. What does the query look like if you capture it in profiler?

Thanks to everyone!

I close this for my part as I use another solution. Anyone interested can dig this further. If you don't use LIKE then stored queries with variables is a nice way to have some programs which can be modified to a certain extent without touching the code.

Mikko (who dislikes LIKE)

Hi Cyborg,

Thank you for your answer! It removes the error, but I got a new error: Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@CNT".

The idea is to fetch the SQL statement from the table and give the input variable in my program. This seems to be hard though.

Best regards,

Mikko

you should have to declare the variables used in your query

DECLARE @CNT varchar(10) SET @CNT = '(3)'

UPDATE LabelReport SET LabelQry = 'SELECT * FROM LabelReport WHERE CompanyIdList LIKE ''%'+@CNT+'%'''

Hi,

Thanks again! I just want the SQL statement in the table. E.g. this works fine:

UPDATE LabelReport SET LabelQry = 'SELECT * FROM LabelReport WHERE Company = @Var1'

I can then use this query and assign @Var1 with a value.

The problem seems to be with the LIKE '%' which fails.

I hope you should go through this article

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

Hi all,

I have chosen another strategy to solve this problem. If anyone gets solution to the execution problem I'm curious. Thanks to everyone!

BR,