I have a very simple stored procedure. When I use a variable in the dynamic sql I get no properties in the ServiceInstance but without the variable it works perfectly. Executing the sql directly in SSMS works fine.
So... this doesn't work (no properties created in the ServiceInstance):
ALTER Procedure [dbo].[ToolNumberDetailListForProduct] @ToolNumbers nvarchar(1024)ASDECLARE @sql nvarchar(1000)SET @sql = 'SELECT * FROM FlattenedProductToolNumber AS ToolNumbers WHERE ToolNumbers.Number IN '+ @ToolNumbersEXEC sp_executesql @sqlGO
This DOES work (properties are generated in the ServiceInstance) and the ONLY thing I changed is replacing @ToolNumbers with '(123456)' :
ALTER Procedure [dbo].[ToolNumberDetailListForProduct] @ToolNumbers nvarchar(1024)ASDECLARE @sql nvarchar(1000)SET @sql = 'SELECT * FROM FlattenedProductToolNumber AS ToolNumbers WHERE ToolNumbers.Number IN '+ '(123456)'EXEC sp_executesql @sqlGO
Ány ideas? I'm tearing my hair out here.
TIA
