SQL query calling user defined function issue

Question

I have the following SQL query in stored procedure and trying to call Table-value function(fn_get_type_ids). I am getting 'Must declare the scalar variable "@category_id".' error message. Table-value function returns muliple IDs. How do I call this function? The stored procedure works if I pass hard coded values to the function. dbo.fn_get_type_ids(2, 90, NULL). It doesn't work if I pass variables shown below. What could be wrong? please suggest.

ALTER PROCEDURE [dbo].[get_search_results]
 @user_id                   BIGINT,
 @category_id           INT = NULL,
 @a_id                  INT = NULL,
 @all_detail_id         INT = NULL

AS

BEGIN

 DECLARE    @select_list        VARCHAR(4000)
 DECLARE    @where_condition    VARCHAR(4000)

 SELECT @select_list =  'SELECT DISTINCT c.table1_id, c.table1_number, c.type_id '
 SELECT @select_list = @select_list + ' FROM dbo.TABLE1 c  '
 SELECT   @select_list = @select_list + ' LEFT JOIN TABLE2 cb ON cb.table1_id = c.table2_id '
 SELECT @where_condition = ' WHERE c.active_flag = 1'
 SELECT @where_condition = @where_condition + ' AND c.type_id in 
 (select type_id from dbo.fn_get_type_ids(@category_id, @a_id, @all_detail_id)) '

END

Possible duplicate with this: stackoverflow.com/questions/2916783/… — Steve
– Steve, Commented Aug 8, 2014 at 14:21

Hiram · Accepted Answer · 2014-08-08 14:25:53Z

1

You are using dynamic sql and sql variables exist in different sessions, to make it work you need to change it to:

SELECT @where_condition = @where_condition + ' AND c.type_id in 
 (select type_id from dbo.fn_get_type_ids('+CAST(@category_id as varchar))+', 
'+CAST(@a_id as varchar))+', '+CAST(@all_detail_id as varchar))+')) '

answered Aug 8, 2014 at 14:25

Hiram

2,6791 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nav100 Over a year ago

Query runs with out errors but also returning category_id NULL values and returning duplicate rows.

Dave.Gugg · Accepted Answer · 2014-08-08 14:14:21Z

0

Try making that last select:

SELECT @where_condition = @where_condition + ' AND c.type_id in 
 (select type_id from dbo.fn_get_type_ids(' + ISNULL(@category_id,'NULL') + ', ' + ISNULL(@a_id,'NULL') + ', ' + ISNULL(@all_detail_id,'NULL') + ')) '

answered Aug 8, 2014 at 14:14

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

1 Comment

nav100 Over a year ago

I received 'Conversion failed when converting the varchar value' error message.

Collectives™ on Stack Overflow

SQL query calling user defined function issue

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related