SQL error: conversion failed when converting nvarchar value

Question

I am attempting to write a fairly simple stored procedure but getting an error when I print @sql

set @where = ' where deliverydate between '''+ @FromDate + ''' and ''' + @ThruDate + ''''
set @where = @where + ' and custlocation = '+ @custlocationID + ''''  

The error reads:

Conversion failed when converting the nvarchar value ' where deliverydate between '8/1/2013' and '8/20/2014' and custlocationID = ' to data type int.

@custlocationID is declared as int.

Can someone help with the apostrophes?

Answer 1

It's not the apostrophes that causes the error message. You are trying to add a string and an integer, and that will implicily convert the string to an integer.

Cast the integer to a string before you concatenate them:

set @where = @where + ' and custlocation = ' + cast(@custlocationID as varchar)