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
Try this then.... UPDATE LabelReport SET LabelQry = 'SELECT * FROM LabelReport WHERE CompanyIdList LIKE ''%''+@CNT+''%'''
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
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)
try this
UPDATE LabelReport
SET LabelQry = 'SELECT * FROM LabelReport WHERE CompanyIdList LIKE ''%'+@CNT+'%'''
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
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 '%'.
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
