Solved

Stored Procedure for Returning Distance Returning 'The data types nvarchar and uniqueidentifier are incompatible in the add operator.'

Posted on 2013-11-05
5
265 Views
Last Modified: 2013-11-12
I am trying to get a list of products using Latitude and Longitude.


The query below is returning the following error, anything jump out?

Msg 402, Level 16, State 1, Procedure sp_GetProductsByLocation, Line 37
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

USE [Flyplyst]
GO
/****** Object:  StoredProcedure [dbo].[sp_SearchCandidatesAdvanced]    Script Date: 11/05/2013 13:32:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetProductsByLocation]

	@RegionId uniqueidentifier,
	@MemberId uniqueidentifier,
	@DaycycleId uniqueidentifier,
	@LystId uniqueidentifier,
	@CenterLatitude NVARCHAR(15) = null,
	@CenterLongitude NVARCHAR(15) = null,
	@Radius NVARCHAR(1000) = null

AS
SET NOCOUNT ON

	DECLARE @v_SQL NVARCHAR(2000)
	DECLARE @v_RadiusMath NVARCHAR(1000)
	DECLARE @earthRadius NVARCHAR(2000)

	SET @earthRadius = '3963.191'

 	SET @CenterLatitude = 29.346675
 	SET @CenterLongitude = -89.42251
 	SET @radius = '500'
 	
	SELECT @v_RadiusMath = 'ACOS((SIN(PI() * ' + @CenterLongitude + ' / 180 ) * SIN(PI() * [t5].[Latitude] / 180)) + (COS(PI() * ' + @CenterLongitude + ' / 180) * COS(PI() * [t5].[Latitude] / 180) * COS(PI()* [t5].[Longitude] / 180 - PI() * ' + @CenterLongitude + ' / 180))) * ' + @earthRadius


SELECT @v_SQL = 'SELECT [t6].[ProductId] AS [productId], [t6].[TagId] AS [tagId], [t6].[Name] AS [name], [t6].[Latitude] AS [latitude], [t6].[Longitude] AS [longitude], [t6].[Name2] AS [brandName], [t6].[Description] AS [description], [t6].[FileName] AS [productImage], [t6].[IsPaying] AS [isPaying], [t6].[AdImage] AS [adImage]' +
'FROM (' +
    'SELECT DISTINCT [t0].[ProductId], [t3].[TagId], [t0].[Name], [t5].[Latitude], [t5].[Longitude], Round(ACOS((SIN(PI() * ' + @CenterLatitude + ' / 180 ) * SIN(PI() * [t5].[Latitude] / 180)) + (COS(PI() * ' + @CenterLatitude + ' / 180) * COS(PI() * [t5].[Latitude] / 180) * COS(PI()* [t5].[Longitude] / 180 - PI() * ' + @CenterLongitude + ' / 180))) * ' + @earthRadius + ', 0) AS ProductDistance, [t3].[Name] AS [Name2], [t0].[Description], [t2].[FileName], [t3].[IsPaying], [t3].[AdImage]' +
    'FROM [dbo].[Product] AS [t0]' +
    'INNER JOIN [dbo].[ProductImage] AS [t1] ON [t0].[ProductId] = [t1].[ProductId]' +
    'INNER JOIN [dbo].[Image] AS [t2] ON [t1].[ImageId] = [t2].[ImageId]' +
    'INNER JOIN [dbo].[Tag] AS [t3] ON [t0].[TagId] = [t3].[TagId]' +
    'INNER JOIN [dbo].[Region] AS [t4] ON [t3].[RegionId] = ([t4].[RegionId])' +
    'INNER JOIN [dbo].[Address] AS [t5] ON [t3].[AddressId] = ([t5].[AddressId])' +
    'WHERE ([t3].[IsActive] = 1) AND ([t0].[IsActive] = 1) AND ([t4].[RegionId] = ' + @RegionId + ') AND (' + @v_RadiusMath + ' <= ' + @Radius + ')' +
    ') AS [t6]' +
'ORDER BY [t6].[IsPaying] DESC, ProductDistance'

EXEC(@v_SQL)
--PRINT(@v_SQL)

Open in new window

USE [Flyplyst]
GO
/****** Object:  StoredProcedure [dbo].[sp_SearchCandidatesAdvanced]    Script Date: 11/05/2013 13:32:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetProductsByLocation]

	@RegionId uniqueidentifier,
	@MemberId uniqueidentifier,
	@DaycycleId uniqueidentifier,
	@LystId uniqueidentifier,
	@CenterLatitude NVARCHAR(15) = null,
	@CenterLongitude NVARCHAR(15) = null,
	@Radius NVARCHAR(1000) = null

AS
SET NOCOUNT ON

	DECLARE @v_SQL NVARCHAR(2000)
	DECLARE @v_RadiusMath NVARCHAR(1000)
	DECLARE @earthRadius NVARCHAR(2000)

	SET @earthRadius = '3963.191'

 	--SET @CenterLatitude = 29.346675
 	--SET @CenterLongitude = -89.42251
 	--SET @radius = '5000'
 	
	SELECT @v_RadiusMath = 'ACOS((SIN(PI() * ' + @CenterLongitude + ' / 180 ) * SIN(PI() * [t5].[Latitude] / 180)) + (COS(PI() * ' + @CenterLongitude + ' / 180) * COS(PI() * [t5].[Latitude] / 180) * COS(PI()* [t5].[Longitude] / 180 - PI() * ' + @CenterLongitude + ' / 180))) * ' + @earthRadius


SELECT @v_SQL = 'SELECT [t6].[ProductId] AS [productId], [t6].[TagId] AS [tagId], [t6].[Name] AS [name], [t6].[Latitude] AS [latitude], [t6].[Longitude] AS [longitude], [t6].[Name2] AS [brandName], [t6].[Description] AS [description], [t6].[FileName] AS [productImage], [t6].[IsPaying] AS [isPaying], [t6].[AdImage] AS [adImage]' +
'FROM (' +
    'SELECT DISTINCT [t0].[ProductId], [t3].[TagId], [t0].[Name], [t5].[Latitude], [t5].[Longitude], Round(ACOS((SIN(PI() * ' + @CenterLatitude + ' / 180 ) * SIN(PI() * [t5].[Latitude] / 180)) + (COS(PI() * ' + @CenterLatitude + ' / 180) * COS(PI() * [t5].[Latitude] / 180) * COS(PI()* [t5].[Longitude] / 180 - PI() * ' + @CenterLongitude + ' / 180))) * ' + @earthRadius + ', 0) AS ProductDistance, [t3].[Name] AS [Name2], [t0].[Description], [t2].[FileName], [t3].[IsPaying], [t3].[AdImage]' +
    'FROM [dbo].[Product] AS [t0]' +
    'INNER JOIN [dbo].[ProductImage] AS [t1] ON [t0].[ProductId] = [t1].[ProductId]' +
    'INNER JOIN [dbo].[Image] AS [t2] ON [t1].[ImageId] = [t2].[ImageId]' +
    'INNER JOIN [dbo].[Tag] AS [t3] ON [t0].[TagId] = [t3].[TagId]' +
    'INNER JOIN [dbo].[Region] AS [t4] ON [t3].[RegionId] = ([t4].[RegionId])' +
    'INNER JOIN [dbo].[Address] AS [t5] ON [t3].[AddressId] = ([t5].[AddressId])' +
    'WHERE ([t3].[IsActive] = 1) AND ([t0].[IsActive] = 1) AND (' + @v_RadiusMath + ' <= ' + @Radius + ')' +
    ') AS [t6]' +
'ORDER BY [t6].[IsPaying] DESC, ProductDistance'

EXEC(@v_SQL)
--PRINT(@v_SQL)

Open in new window

0
Comment
Question by:Rob Gaudet
[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
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39626536
Where you create your @v_SQL, you need to implicitly convert to NVARCHAR
For instance, instead of
....+ @RegionId + ....
DO
.... + CONVERT(NVARCHAR(36, @RegionId) + ....
0
 

Author Comment

by:Rob Gaudet
ID: 39627811
RegionId is a uniqueidentifier. What is reason for your suggestion?
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39628096
Conversion from uniqueidentifier to varchar is not implicit.
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 39628103
You have to convert it, that's the way it was written.
0
 

Author Closing Comment

by:Rob Gaudet
ID: 39642079
Thank you, worked.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

762 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