question

mlipsane avatar image
mlipsane asked

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
t-sqlupdatelike
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Try this then.... UPDATE LabelReport SET LabelQry = 'SELECT * FROM LabelReport WHERE CompanyIdList LIKE ''%''+@CNT+''%'''
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
how are you executing the sql from your 'program'?
0 Likes 0 ·
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; }
0 Likes 0 ·
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?
0 Likes 0 ·
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)
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
try this

 UPDATE LabelReport
 SET LabelQry = 'SELECT * FROM LabelReport WHERE CompanyIdList LIKE ''%'+@CNT+'%'''
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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 '%'.
0 Likes 0 ·
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+'%'''
0 Likes 0 ·
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.
0 Likes 0 ·
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,
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.