(SQL) ERROR: Argument data type sql_variant is invalid for argument 1 of like function.

Hi Guys,

I getting Error: Argument data type sql_variant is invalid for argument 1 of like function.


DECLARE @Tbl1 as TABLE ([VALUE] int)	
BEGIN
	INSERT into @Tbl1 Select 

// how to fix this check if transformId is int and value is string
	[VALUE]	= CASE WHEN d.transformId = 22 AND f.value like 'Test_file%' then d.transformId END	

FROM documents d left join fValues f on f.docid = d.doctId
END
SELECT count([VALUE]) FROM @Tbl1

Open in new window


Thank You.
DariusAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
SQL_Variants won’t work with LIKE in a WHERE clause. Try.

1.

DECLARE @Tbl1 AS TABLE ([VALUE] INT)	

	INSERT INTO @Tbl1 
	Select CASE WHEN d.transformId = 22 AND CAST(f.value AS VARCHAR(1000)) like 'Test_file%' THEN d.transformId END
	FROM documents d left join fValues f ON f.docid = d.doctId

SELECT count([VALUE]) FROM @Tbl1

Open in new window


2.

--

DECLARE @Tbl1 as TABLE ([VALUE] INT)	

INSERT into @Tbl1 	
SELECT CASE WHEN d.transformId = 22 AND CHARINDEX(CAST(f.value AS VARCHAR(1000)), 'Test_file' , 0) > 0 THEN d.transformId END	
FROM documents d LEFT JOIN fValues f on f.docid = d.doctId

SELECT COUNT([VALUE]) FROM @Tbl1
--

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Can you post your table structure of documents and fValues.
0
 
DariusAuthor Commented:
Pawan,
both queries return 0 values. Definitely I have many transformId(22) and values (Test_file%)on this transform.

Documents table:
docId(PK,bigint,not null)
dateAdded(datetime, not null)
transformId(int, not null)

fValues Table:
fieldValuesId(PK,bigint, not null)
docId(bigint, not null)
values(sql_variant, null)

Thanks
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Pawan KumarDatabase ExpertCommented:
Okies, let me work on this..
1
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Here is the complete script.


--


CREATE TABLE Documents
(
	 docId bigint not null
	,dateAdded datetime not null
	,transformId int not null
)
GO

CREATE TABLE fValues
(
	 fieldValuesId bigint Not null
	,docId bigint not null
	,[values] sql_variant
)
GO

INSERT INTO Documents VALUES (1,GETDATE(),22)
GO

INSERT INTO fValues VALUES (101,1,'Test_file_Pawan')
GO

--

Open in new window


1. Solution 1

DECLARE @Tbl1 as TABLE ([VALUE] INT)	

INSERT into @Tbl1 	
SELECT CASE WHEN d.transformId = 22 AND CHARINDEX('Test_file' , CAST(f.[values] AS VARCHAR(1000)), 0) > 0 THEN d.transformId END Vals
FROM documents d LEFT JOIN fValues f on f.docid = d.docId

SELECT COUNT([VALUE]) FROM @Tbl1

GO

Open in new window


2. Solution

--

DECLARE @Tbl1 as TABLE ([VALUE] INT)	

INSERT into @Tbl1 
SELECT CASE WHEN d.transformId = 22 AND CAST(f.[values] AS VARCHAR(1000)) LIKE 'Test_file%' THEN d.transformId END Vals
FROM documents d LEFT JOIN fValues f on f.docid = d.docId

SELECT COUNT([VALUE]) FROM @Tbl1
--

Open in new window


Output

--------------

Vals
22
0
 
DariusAuthor Commented:
Thanks!

Second solution works for me.
And it works previous second solution as well. Sorry I did mistake by providing wrong transform number.
0
 
Pawan KumarDatabase ExpertCommented:
Great that it works for you !! Enjoy ..
0
 
DariusAuthor Commented:
Dhanyawad
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
values(sql_variant, null)
Why are you using sql_variant? This means that values than store any kind of data type so when is not char the like operation will fail.

For your issue you'll need to only execute the LIKE operation for varchar data type so you'll need to add the following filter:
DECLARE @Tbl1 as TABLE ([VALUE] int)	
BEGIN
	INSERT into @Tbl1 
        Select  [VALUE] = CASE 
                      WHEN d.transformId = 22 AND f.value like 'Test_file%' then d.transformId 
                      END	
        FROM documents d 
                  left join fValues f on f.docid = d.doctId
        WHERE SQL_VARIANT_PROPERTY(f.value,'BaseType') = 'VARCHAR'
END
SELECT count([VALUE]) FROM @Tbl1

Open in new window

0
All Courses

From novice to tech pro — start learning today.