Mcbaloo Mcbaloo - 4 years ago 198
SQL Question

The data types varchar and varbinary(max) are incompatible in the add operator. while trying to create stored procedure

Trying to create a stored procedure in sql server but i'm getting this error. The data types varchar and varbinary(max) are incompatible in the add operator.

CREATE PROC dbo.SearchAnswers
@Name varchar(225),
@Address varchar(225),
@ServiceRendered varchar(225),
@spportedHMO varchar(225),
@States varchar(225),
@Country varchar(225),
@OtherServices varchar(225),
@Hospital_Image varbinary(225)
AS
SELECT *
FROM Hospital
WHERE Name LIKE '%' + (@Name) + '%'+''
OR Address LIKE ''+'%' + (@Address) + '%'+''
OR ServiceRendered LIKE ''+'%' + (@ServiceRendered) + '%'+''
OR spportedHMO LIKE ''+'%' + (@spportedHMO) + '%'+''
OR States LIKE '' +'%' + (@States) + '%'+''
OR Country LIKE ''+ '%' + (@Country) + '%'+''
OR OtherServices LIKE ''+'%' + (@OtherServices) + '%'+''
OR Hospital_Image LIKE ''+'%' + Cast(@Hospital_Image as varbinary(max)) + '%'

Answer Source

You don't need LIKE wwhen searching Hospital_Image. Change it to '='. Possibly review if you need LIKE for any fields; only use LIKE to search for the string contained in the field:

CREATE PROC dbo.SearchAnswers
    @Name varchar(225),
    @Address varchar(225),
    @ServiceRendered varchar(225),
    @spportedHMO varchar(225),
    @States varchar(225),
    @Country varchar(225),
    @OtherServices varchar(225),
    @Hospital_Image varbinary(225)
AS
SELECT *  
FROM Hospital
WHERE Name LIKE '%' + (@Name) + '%'+'' 
    OR Address LIKE ''+'%' + (@Address) + '%'+'' 
    OR ServiceRendered LIKE ''+'%' + (@ServiceRendered) + '%'+'' 
    OR spportedHMO LIKE ''+'%' + (@spportedHMO) + '%'+'' 
    OR States LIKE '' +'%' + (@States) + '%'+'' 
    OR Country LIKE ''+ '%' + (@Country) + '%'+'' 
    OR OtherServices LIKE ''+'%' + (@OtherServices) + '%'+'' 
    OR Hospital_Image = @Hospital_Image
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download