Pass Values to a Procedure Input Param from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pass Values to a Procedure Input Param from table

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-07 : 16:59:05

Hi,

I have a procedure with 2 input parameters:

Create Procedure TelephoneCalls (Numberof Calls int, DateofCall DATETIME)

I am looking to execute the stored procedure with different parameters passed from a table. I want to execute this in a loop so the stored procedure executes for all the parameters.

How do I achieve that??

thanks!

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-07 : 17:13:28

Well, if you are using SQL Server 2008, you can use tables as parameters.

Barring that, you could pass in the list of values as delimited strings, split them up into tables using a split function, and join on that.

You could populate tables with your selected values, and join on those tables. Pretty much the same as the previous answer, just with permanent tables.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-07 : 17:27:00

Thanks Tim - I am still a little unclear though. If I want to use one table that has all the values that I need to input then how exactly do I execute the procedure to get the values from this table. For example the table looks like that:

Calls ID DateofCall
3 , 2010-12-01
4 ,2010-12-02
5 , 2010-12-03

So I want the input in the procedure with these sets of values : (3, 2010-12-01), (4, 2010-12-02), (5, 2010-12-03)

How do I achieve that?
thanks

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 10:14:04

Another way to formulate the question is "How can I read a table 1 line at a time and use these values for a procedure's parameters input values?"

Thanks!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 13:11:28

Anyone please? I am really stuck on the issue of passing values from a table to procedure input parameters! Thanks much!

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-08 : 13:25:19

Well that's not really a good question

You ALWAYS want, whenever possible to do set based procession.

So you have a table...fine

What is the procedure suppose to do?

We haven't seen it yet

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-08 : 13:30:29

This will generate SQL:

SELECT 'EXEC TelephoneCalls ' + cast(ID as varchar) + ', ''' + convert(char(8), dateOfCall, 112) + ''';'
FROM Calls

You'll have to copy and paste the results and run them in a new window.

I have a feeling (like Tim does) that your code can be written to work on a set of call data, instead of individual calls, and it would probably perform much better that way.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 13:34:06

Ok - the procedure is supposed to use the values in a few simple calculations and output the results from the calculation into a new table:
Here is the exact procedure as well:

Create Procedure Test (@CallsLastWeek_Input int, @NumberPaymentsLastWeek_Input int, @TotalActivityNextMonth_Output int OUTPUT)

as

Declare @Calls_Payments Table (TotalActivity_NextMonth int)

Declare @CallsNextWeek_Value int

set @CallsNextWeek_Value = @CallsLastWeek_Input + 5

Declare @NumberpaymentsNextWeek int

Set @NumberpaymentsNextWeek = @NumberPaymentsLastWeek_Input - 3

set @TotalActivityNextMonth_Output = (@NumberpaymentsNextWeek + @CallsNextWeek_Value)* 4

Insert into @Calls_Payments
Values (@TotalActivityNextMonth_Output)

select * into TotalActivityTest
from @Calls_Payments

select * from TotalActivityTest

End of procedure

I actually found a way to pass the values from the table with the parameters but now I need to do this in a loop executing one variable set at a time. here is what I have so far:

Begin
Declare @TotalActivityNextMonth_Output int
Declare @Calls int
Declare @Payments int

Set @calls = (select calls_last_week from dbo.TestNumbers where Test# = 1)
set @Payments = (select NumberofPaymentsLastWeek from dbo.TestNumbers where Test# = 1)

exec Test @calls, @Payments, @TotalActivityNextMonth_Output

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-08 : 13:57:18

What table are the values coming from?

TestNumbers

And you want the results in

TotalActivityTest

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-08 : 14:00:28

Is this all you want?


select    (calls_last_week + 5) + (NumberofPaymentsLastWeek - 3) * 4
  from dbo.TestNumbers where Test# = 1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 14:11:42

TestNumbers is a table I created that holds the value sets that I want to input into the procedure parameters. the table looks like that:

Test# Calls_Last_Week NumberofPaymentsLastWeek
1 300 90
2 250 100
3 410 110

All I want is to be able to execute the procedure for all 3 sets of values (Test# 1, 2, 3) one set at a time. And yes - I want the results into the table TotalActivityTest.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-08 : 15:21:46

Unless I'm way off base, the following statement will do the whole thing:

INSERT TotalActivityTest
SELECT (calls_last_week + NumberofPaymentsLastWeek + 2) * 4
FROM dbo.TestNumbers

A bit shorter I'd say.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 15:34:44

well actually I want to use the procedure to insert values into the table. I am building a model and this is just test data.

The biggest problem I am facing currently is to be able to pull values from the parameter-table TestNumbers one row at a time!

Currently I am manually specifying which line's values to pull but I want to automate this so that I am executing the procedure with input parameters passed from the table one row at a time!

Here is what I have

Declare @TotalActivityNextMonth_Output int
Declare @Calls int
Declare @Payments int
Declare @TestNumber int

Set @calls = (select calls_last_week from dbo.TestNumbers where Test# = 2 )
set @Payments = (select NumberofPaymentsLastWeek from dbo.TestNumbers where Test# = 2)
set @TestNumber = (select Test# from dbo.TestNumbers where Test# = 2)

exec Test @calls, @Payments, @TestNumber, @TotalActivityNextMonth_Output

So on this execution, I am changing the clause where Test# = 2, with different values to execute the procedure multiple times. I think I need to use some type of loop statement so that I can run the procedure with variables from all rows from the parameter-table..Does this make sense for you?

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 15:43:12

In other words, I want to create a looping statement that will retrieve one row of values from parameter-table TestNumbers, then execute procedure with these values as input parameters, then go back and take a second row from the TestNumbers table, execute the procedure again, then go back...etc... Is this possible at all??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-08 : 15:54:46

Did you see my original reply? Will this not work for you?

quote:
Originally posted by robvolk

This will generate SQL:
SELECT 'EXEC TelephoneCalls ' + cast(ID as varchar) + ', ''' + convert(char(8), dateOfCall, 112) + ''';'
FROM Calls
You'll have to copy and paste the results and run them in a new window.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 15:56:53

I dont understand this - why do I need to convert column types?

what is the purpose of this execution and how will it solve my problem with returning one row of values at a time? thanks!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 16:38:45

Rob - would please explain what you meant by the above statement? thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-08 : 16:43:21

I'm not clear about your goal, so let me ask which is more important:

1. that you get all the results of the procedure into the TotalActivityTest table
2. that you run this in a loop

I'm avoiding solutions that process data in a loop because it is very inefficient to do this kind of processing in SQL Server. Unless you have a compelling reason, or you have procedural logic that cannot be done in a set-based manner, you're better off avoiding loops and cursors.

The procedure you posted can be modified to perform in a set-based manner. If your actual code is different, please post it.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 16:55:03

I don't necessary need to run this into a loop but this is the only solution that I can come up with (although it doesnt work in reality). The main goal is to execute the procedure multiple times with the different values sets retrieved from the parameter-table. The results of these procedures have to be placed in the totalactivitytable.

What do you mean by set-based manner? Can you give me an example? You can use the code I have posted above! thanks!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 16:56:17

Actually here is an update procedure:

Create Procedure Test (@CallsLastWeek_Input int, @NumberPaymentsLastWeek_Input int, @TestNo int, @TotalActivityNextMonth_Output int OUTPUT)

as

Declare @Calls_Payments Table (TotalActivity_NextMonth int)

Declare @CallsNextWeek_Value int

set @CallsNextWeek_Value = @CallsLastWeek_Input + 5

Declare @NumberpaymentsNextWeek int

Set @NumberpaymentsNextWeek = @NumberPaymentsLastWeek_Input - 3

set @TotalActivityNextMonth_Output = (@NumberpaymentsNextWeek + @CallsNextWeek_Value)* 4

Update TotalActivityTest
Set OutputValues = @TotalActivityNextMonth_Output
where ActivityNo = @TestNo

____

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-08 : 17:00:25

quote:
What do you mean by set-based manner? Can you give me an example?

The code in my 2nd post:

INSERT TotalActivityTest
SELECT (calls_last_week + NumberofPaymentsLastWeek + 2) * 4
FROM dbo.TestNumbers

Should accomplish the same thing as running the stored procedure for each row of data in the TestNumbers table, as you requested. There's no need for a loop, in can be done in one operation, no matter how many rows there are in TestNumbers. That's what set-based means: it processes a set of data, not individual rows of data.

Please try running it (if you haven't already) and let me know if the results are correct. If they are, you can probably eliminate the stored procedure entirely.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources