Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-13
9
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 30

Expert Comment

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

Accepted Solution

by:
Pawan Kumar earned 2000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 30

Expert Comment

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

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 2000 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 30

Expert Comment

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

Author Comment

by:Darius
ID: 41841746
Dhanyawad
1
 
LVL 52

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 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