Error converting data type nvarchar to numeric

Transact-SQL
1

Hi
I have this in my stored procedure...

DECLARE @AvdId nVarChar(500)

AND (';'+@AvdId+';' LIKE '%;'+CAST(dbo.SurveyAnswerInfo.AvdId AS VARCHAR(32))+';%') OR @AvdId = ''

And I pass in (SET @AvdId = '')

But if I pass in empty value I get a "Error converting data type nvarchar to numeric" error, I also tried to use OR @AvdId = '0', but then I don't get the result I should retrieve. What am I missing here?

2

Not sure but (not enough from what you supplied ) , try with :

@AvdId = CAST('0' AS NVARCHAR(30)),

3

Sorry, it removes the error but I dont get any results which I should do. What more info should I post?

4

Best , is to give more info about it.
Follow the link http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

5 
--- Create Table

CREATE TABLE [dbo].[SurveyAnswerInfo](
	[SurveyAnswerInfoID] [int] IDENTITY(1,1) NOT NULL,
	[DateAdded] [smalldatetime] NULL,
	[AvdId] [int] NULL



-- Add some data	
INSERT INTO	SurveyAnswerInfo ('2015-04-02 09:08:00') VALUES ('2015-04-02 09:08:00', NULL)
INSERT INTO	SurveyAnswerInfo ('2015-05-02 09:08:00') VALUES ('2015-04-02 09:08:00', 1)
INSERT INTO	SurveyAnswerInfo ('2015-05-02 09:08:00') VALUES ('2015-04-02 09:08:00', 1)
INSERT INTO	SurveyAnswerInfo ('2015-06-02 09:08:00') VALUES ('2015-04-02 09:08:00', 2)
INSERT INTO	SurveyAnswerInfo ('2015-06-02 09:08:00') VALUES ('2015-04-02 09:08:00', NULL)



-Query

DECLARE	@CompanyIDs nVarChar(500)
DECLARE	@AvdId nVarChar(500)
DECLARE	@Date1 SmallDateTime
DECLARE	@Date2 SmallDateTime

DECLARE	@Temp SMALLDATETIME
DECLARE @FromDate SMALLDATETIME
DECLARE @ToDate SMALLDATETIME


SET	@CompanyIDs = 1129
SET	@AvdId = ''
SET	@Date1 = '2015-03-15 00:00:00'
SET	@Date2 = '2015-06-15 00:00:00'

SET @FromDate = @Date1
SET @ToDate = @Date2 + 1 

-- If @FromDate is later than @ToDate, switch the two dates
IF @FromDate > @ToDate
	SELECT	@Temp = @FromDate,
		@FromDate = @ToDate,
		@ToDate = @Temp

-- Remove the time portion and set @ToDate to one day later
SELECT	@FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
	@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @ToDate), '19000101')

SELECT        Count(SurveyAnswerInfo.CompanyID) As Qty
FROM            dbo.SurveyAnswerInfo


WHERE  ';'+@CompanyIDs+';' LIKE '%;'+CAST(dbo.SurveyAnswerInfo.CompanyID AS VARCHAR(32))+';%' 
AND (dbo.SurveyAnswerInfo.DateAdded >= @FromDate) AND (dbo.SurveyAnswerInfo.DateAdded < @ToDate)
AND (';'+@AvdId+';' LIKE '%;'+CAST(dbo.SurveyAnswerInfo.AvdId AS VARCHAR(32))+';%') OR @AvdId = CAST('0' AS NVARCHAR(32))

-- This should give Qty = 5 when I'm not passing any value for @AvdId, but if I pass @AvdId = '1;2', then it should count 3, and if I pass @AvdId = '1;' it should count 2

6

I found the issue... I needed to change to the following...

AND (';'+@AvdId+';' LIKE '%;'+CAST(dbo.SurveyAnswerInfo.AvdId AS VARCHAR(32))+';%' OR @AvdId = '')