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
261 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

8 Experts available now in Live!

Get 1:1 Help Now