x

update row with SQL statement with LIKE '%' fails

Hi there,

I'm running MS SQL2008 and I want to store an SQL statement in the DB. The statement works OK in the query window:

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

SELECT * FROM LabelReport WHERE CompanyIdList LIKE '%'+@CNT+'%'

However I can't make it in the UPDATE...

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

fails with "The data types varchar and varchar are incompatible in the modulo operator."

Best Regards,

Mikko
more ▼

asked Sep 09, 2010 at 12:32 AM in Default

mlipsane gravatar image

mlipsane
23 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Try this then....

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

answered Sep 09, 2010 at 01:19 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

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
Sep 09, 2010 at 03:57 AM mlipsane
how are you executing the sql from your 'program'?
Sep 09, 2010 at 04:16 AM Kev Riley ♦♦

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;
}
Sep 09, 2010 at 04:26 AM mlipsane
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?
Sep 09, 2010 at 04:33 AM Kev Riley ♦♦

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)
Sep 09, 2010 at 05:28 AM mlipsane
(comments are locked)
10|1200 characters needed characters left
try this
  UPDATE LabelReport  SET LabelQry = 'SELECT * FROM LabelReport WHERE CompanyIdList LIKE ''%'+@CNT+'%''' 
more ▼

answered Sep 09, 2010 at 12:41 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

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
Sep 09, 2010 at 12:54 AM mlipsane

Hi again,

In the table I only have a query and it works fine if I e.g.: UPDATE LabelReport SET LabelQry = 'SELECT * FROM LabelReport WHERE Company = @Var1'

The LabelQry gets stored in the table and sql server does not ask any declarations. The problem is with LIKE and '%'.
Sep 09, 2010 at 01:04 AM mlipsane

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+'%'''
Sep 09, 2010 at 12:56 AM Cyborg

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.
Sep 09, 2010 at 01:08 AM mlipsane
Sep 09, 2010 at 01:21 AM Cyborg

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,
Sep 09, 2010 at 05:05 AM mlipsane
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x133
x22

asked: Sep 09, 2010 at 12:32 AM

Seen: 4931 times

Last Updated: Sep 09, 2010 at 01:03 AM