I have the following SProc which works fine when executing in SQL:
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql varchar(255)
-- Insert statements for procedure here
set @sql = 'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By FROM Products'
If @catId <> 0
set @sql = @sql + ' WHERE Cat_ID = '+@catId
set @sql = @sql +' ORDER BY Sequence'
However, when it gets called from the DataSource on my DataList (code attached), it comes up with the following error:
Conversion failed when converting the nvarchar value
'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By
WHERE Cat_ID = ' to data type int.
I'm not attempting to convert to an Int so why on earth am I getting this? I can only assume it must be an ASP.Net issue??