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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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