Passing SQL function parameters

Question

I have made an SQL function that calculates different data and it looks something like this:

Declare @parameter1 float, @parameter2 float, @parameter3 float, @parameter4 float

SET @parameter1 = 3
SET @parameter1 = 5
SET @parameter1 = 7
SET @parameter1 = 9

dbo.SqlFuntion(@parameter1, @parameter2, @parameter3, @parameter4)

Now this works just fine, my challenge is that I want my parameters to be retrieved from a database table instead of declaring them manually.

So I want something like:

 Select name, age, postal, weight from TableValues where ID = 1234 

 dbo.SqlFuntion(name, age, postal, weight)

Can anyone tell me how this can be done, without declaring anything?

Why not just pass the ID to the function and put the query inside it? — Rachcha
– Rachcha, Commented Jan 7, 2014 at 9:32
Without declaring anything this is going to be tough, if you insist on not declaring variables you need to put value selection logic into the function itself; though it may very well be you will need to declare variables there then - but that largely depends on what you do with those values exactly in your function — DrCopyPaste
– DrCopyPaste, Commented Jan 7, 2014 at 10:00

Steve Wilkes · Accepted Answer · 2014-01-07 09:57:01Z

2

You can do it like this:

Declare @parameter1 float, @parameter2 float, @parameter3 float, @parameter4 float

Select @parameter1 = name, @parameter2 = age, @parameter3 = postal, @parameter4 = weight
from TableValues where ID = 1234 

dbo.SqlFuntion(@parameter1, @parameter2, @parameter3, @parameter4)

edited Jan 7, 2014 at 9:57

Steve Wilkes

7,1433 gold badges31 silver badges33 bronze badges

answered Jan 7, 2014 at 9:32

Talha

19.3k8 gold badges53 silver badges66 bronze badges

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

4 Comments

Rachcha Over a year ago

You're still declaring :P

DrCopyPaste Over a year ago

@Rachcha if you want to pass parameters to your function you need to declare them first; the syntax in the original question is flawed - this answer shows how you can assign values to variables via a query; if you do not want to declare extra variables you would need to make your function interacting with that table directly (i.e. executing a very similar select to retrieve the values to work with)

pancake Over a year ago

Thanks mate!, I will try this aswell and get back to u soon!

pancake Over a year ago

Sorry for the late accept, but i just got it working! thanks mate

Dharmraj · Accepted Answer · 2014-01-07 10:06:20Z

1

// YOU CAN DO IT USING CURSOR, IT COULD HELP YOU

DECLARE v_done INT DEFAULT FALSE;
DECLARE v_name VARCHAR(50);
DECLARE v_age DOUBLE;
DECLARE v_postal VARCHAR(10);
DECLARE v_weight DOUBLE;

DECLARE cur1_cursor CURSOR FOR SELECT name, age, postal, weight from TableValues where ID = 1234 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

OPEN cur1_cursor;
    read_loop: LOOP

        FETCH cur1_cursor INTO v_name,v_age,v_postal,v_weight;

        IF v_done THEN
          LEAVE read_loop;
        END IF;

        SET @v_name = v_name;
        SET @v_age = v_age;
        SET @v_postal = v_postal;
        SET @v_weight = v_weight;

        dbo.SqlFuntion(@v_name, @v_age, @v_postal, @v_weight);  

END LOOP;

answered Jan 7, 2014 at 10:06

Dharmraj

1741 silver badge16 bronze badges

3 Comments

DrCopyPaste Over a year ago

doing this with a cursor is just obfuscating the hell out of a simple SELECT @parameter = column FROM TABLE, besides that what's the benefit? xD

Dharmraj Over a year ago

its depends upon requirement and your choice, its just another way to do this.

pancake Over a year ago

Thanks alot i will try your example and i will get back to u with a feedback!

Collectives™ on Stack Overflow

Passing SQL function parameters

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related