CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
nav100
nav100

Reputation: 3133

SQL query calling user defined function issue

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

Upvotes: 0

Views: 106

Answers (2)

Hiram
Hiram

Reputation: 2679

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))+')) '

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

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') + ')) '

Upvotes: 0

Related Questions