Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
273 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 1500 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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