samedi 14 novembre 2015

Using a variable and pattern matching in SQL LIKE

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.

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.

I did the following snippet but I am getting this error "Conversion failed when converting the varchar value '%_' to data type int."

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

Aucun commentaire:

Enregistrer un commentaire