Reputation: 25
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?
Upvotes: 0
Views: 43
Reputation: 176189
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
Upvotes: 1