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
262 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Anyway to make "All" the default in the dropdown? 6 34
ASP.NET 5 Templates 2 66
Where to download and how to install sqldmo.dll 5 34
Sql Query Datatype 2 19
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now