title: Using a variable and pattern matching in SQL LIKE
body: <p>I have a sql table where one of the columns in 'Action' an nvarchar(max). This column can have values like HelloKitty_1.MP4 or HeMan_2.MP4, or SpiderMan_1.MP4, Hulk_4.MP4.. Here 1, 2, 1 is what I call videoIndex.</p>
  
  <p>I am writing a stored procedure in SQL where I pass in the videoIndex (int) and it returns me all rows with that index. So in example above, if  pass in 1, it should return me HelloKitty_1.MP4 and SpiderMan_1.MP4.</p>
  
  <p>I did the following snippet but I am getting this error "Conversion failed when converting the varchar value <code>'%_'</code> to data type int."</p>
  
  <pre><code>DECLARE @SearchPattern varchar(40)  
  SET @SearchPattern = '%' + '_' + @videoindex + '.MP4'  
  
  DECLARE @WatchVideoCount int;  
  SET @WatchVideoCount =   
       (SELECT count(*)
       FROM [dbo].[VideoWatched]
       WHERE 
       [Action] LIKE @SearchPattern)  
  
  SELECT @WatchVideoCount as TotalTimesUsersWatchedVideo, @videoindex as videoIndex
  </code></pre>