Call tabled function with parameters in sql

I’m a newbie in SQL and with programming languages in general. I’m trying to make a tabled function in SQL (SQL Server):

CREATE FUNCTION dbo.fn_Get_List (
    @PAR_user_code INT
    , @PAR_id_session INT
    , @PAR_id_profile INT              
    , @PAR_days_check_from DATETIME 
    , @PAR_days_check_to DATETIME  
    , @PAR_register BIT
)
RETURNS @tb_return table(
    num_prat int,                   
    num_ipotec int,                 
    typeipotec tinyint,             
    fee money,         
    stipulated_date smalldatetime,       
    expire_date smalldatetime,    
    renew_date datetime,  
    delete_date date,        
    authentication_date date,            
    prime money)                 
AS
BEGIN

and then I have to call it in another sql page. I’ve tried to use this syntax:

DECLARE @PAR_user_code INT
DECLARE @PAR_id_session INT
DECLARE @PAR_id_profile INT              
DECLARE @PAR_days_check_from DATETIME 
DECLARE @PAR_days_check_to DATETIME  
DECLARE @PAR_register BIT

SELECT * 
FROM fn_IPO_Get_Elenco_Ipoteche(@PAR_user_code,@PAR_id_session,@PAR_id_profile,@PAR_days_check_from,@PAR_days_check_to,@PAR_register)

If I run my SELECT I don’t have any result, because my parameters are not initialized. How can I insert values into them? What I want to do is take values from a table that I’ve created and where I have these attributes and when I run my webpage I’ll fill them.

For example, if I log with ID Session = 1, I’d like to see it into @PAR_id_session and then valorise the other parameters with the user’s choices on the webpage (I have multiple choice for @PAR_days_check_from). How can I do it if I don’t know the values?

Answer

Intialization of variable in T-SQL:

DECLARE @PAR_user_code INT = 1;

or by using SET:

SET @PAR_user_code = 1;

The function arguments can be populated from table using CROSS/OUTER APPLY:

SELECT *
FROM my_table t
CROSS APPLY fn_IPO_Get_Elenco_Ipoteche(t.user_code, ...) f
WHERE t.col = ? -- any condition

Advertisement

Answer