SQL Server Service Stored Procedures
Author: firstname.lastname@example.org (Iain Sharp)
Hi, I want to define and use an SSP on $MSS, I want to pass in a single string, and get an entity parameter back. I can define the procedure as (effectively) create procedure get_stock_levels @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels and set up a signature with one table valued parameter. When run the table painted on the component is populated. I wish to amend the procedure to (effectively) alter procedure get_stock_levels @product_pattern varchar(50), @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels where prd_cd like @product_pattern When I amend the signature to have two parameters, a basic string in parameter with a literal name of either @product_pattern or product_pattern I get an error status of -150, and the message log shows "ICCDRV-MSS-ERR Call ICC component failed" Can anyone tell me what I'm doing wrong here? We need to do this so we can embed the selection parameter in the (complex) sql query which I have replaced with 'stv_stock_levels' above. Otherwise the sql server optimiser cannot switch to using index seeks and returns waaay too much data.