Solved

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

Posted on 2016-10-13
9
81 Views
Last Modified: 2016-10-13
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.
0
Comment
Question by:Darius
  • 5
  • 3
9 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41841713
Can you post your table structure of documents and fValues.
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41841717
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
 

Author Comment

by:Darius
ID: 41841728
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41841729
Okies, let me work on this..
1
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41841735
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
 

Author Comment

by:Darius
ID: 41841743
Thanks!

Second solution works for me.
And it works previous second solution as well. Sorry I did mistake by providing wrong transform number.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41841745
Great that it works for you !! Enjoy ..
0
 

Author Comment

by:Darius
ID: 41841746
Dhanyawad
1
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41841751
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Remove last comma in SQL Server column with Type Of nvarchar(Max) 3 28
Run an action on recently added records to a table 13 65
SQLCMD Syntax 2 15
job schedule 8 20
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question