Multiple call of stored procedures

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

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple call of stored procedures

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-23 : 23:52:46

Iam really stuck here..I have 2 stored procedures.

select @emp_name=emp_name,@emp_age=emp_age,@sex=sex,@address=address
from tbl_emp_details
Here the stored procedure will insert into a table in a different table but here I need to call only once
1. usp_emp_details @emp_name,@emp_age,@sex,@address

Now the second one it picking from tbl_emp_policies where for a emp_id there is more records now i have to call this stored procedure many times (basically the number of emp_ids present for an employer)

Here for the select I have 3 records for emp_id and it varies for different emp_ids so how call this stored procedure accordingly

select @emp_year=emp_year,@emp_comments=emp_comments
from tbl_emp_policies where emp_id=@emp_id

Here I have 2 or 3 records according to emp_id

2. usp_emp_policies @emp_id,@emp_year,@emp_comments

How can I call this multiple times

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-24 : 01:45:44

i'm really confused with the question but are you referring to:

insert into table1
select <fields> from table2?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 02:01:48

basically I have a select statement which passes 2 to 3 rows for a Id and now I need to call a stored procedure which accepts one by one row..I don't know may be a cursor will be a solution

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 02:11:05

Storing each row and passing each row item to the stored procedure..basically calling the stored procedure multiple times according to the number of rows present

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-24 : 02:35:48

so you're getting rows according to emp_id, then inserting them into another table? wherein this table will be queried again and the result inserted to another table?

insert into tbl_emp_details
select <fields here> from tablesource
where emp_id= @id

insert into tbl_emp_policies
select <fields here> from tbl_emp_details
where emp_id=@id

is this it, if not, how about providing the table structures and some sample data?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 03:38:11

Its not insertion of data to another table .Here the problem is according to the number of records i should call the stored procedure multiple times which uses the rows as the parameters

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 03:49:44

declare @emp_class varchar(50)
declare @emp_mark_total varchar(50)
declare @emp_year varchar(50)

select @emp_class=emp_class,@emp_mark_total=emp_mark_total,@emp_year=emp_year from emp_grade where emp_id=@emp_id
Now there are 5 records for this select statement
Now I have to call the stored procedure 5 times and pass the corresspondong row set each time as the parameters values
Execute usp_emp_class @emp_class,@emp_mark_total,@emp_year

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 03:50:52

Hi Jen ,I hope the explanation helps for the solution

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-24 : 04:40:50

quote:
Originally posted by sqllearner

declare @emp_class varchar(50)
declare @emp_mark_total varchar(50)
declare @emp_year varchar(50)

select @emp_class=emp_class,@emp_mark_total=emp_mark_total,@emp_year=emp_year from emp_grade where emp_id=@emp_idNow there are 5 records for this select statement
Now I have to call the stored procedure 5 times and pass the corresspondong row set each time as the parameters values
Execute usp_emp_class @emp_class,@emp_mark_total,@emp_year

you're emp_id is not unique? well to each his own.

actually you don't need the cursor. if you can save the rows in a table then use a while loop so that you'll call the next sproc n times

declare @curRow int,@TotalRows int

select identity(int,1,1) RowNum,<fields> into #tempTable from emp_grade
where emp_id=@emp_id
set @TotalRows=@@rowcount + 1 'i usually add one more value so i won't have to use the <=

set @curRow=1
while @curRow<@TotalRows
begin
'call your sproc here and get the parameters you need as "select <fields> from #tempTable where rownum=@curRow"
set @curRow=@curRow+1
end

others here may have a better idea...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-24 : 06:03:14

try this. play with it


declare @List varchar(8000)
select 	@List = coalesce(@List, '') + 'exec(''usp_emp_class ''''' + cast(emp_class as varchar(500)) + ''''', ''''' + 
	cast(emp_mark_total as varchar(50)) + ''''', ''''' + cast(emp_year as varchar(50)) +  +''''');'
from 	emp_grade where emp_id=@emp_id
exec('''' + @List + '''')

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 12:23:17

Its giving tis error and I can't find out whats the problem

Line 1: Incorrect syntax near ''.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 13:06:19

HI jen,
When I use ur script its gives me an error as one fo the column is a int
Server: Msg 8114, Level 16, State 4, Procedure usp_issues_deal, Line 0
Error converting data type nvarchar to int.
Server: Msg 8114, Level 16, State 4, Procedure usp_issues_deal, Line 0
Error converting data type nvarchar to int.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 15:05:15

any idea of the errors spirit and jen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-24 : 15:16:56

you have an ' missing somewhere....
the end result should look like:
exec('exec('usp_emp_class 'value1', 'value2', 'value3''');exec('usp_emp_class 'value1', 'value2', 'value3''');')

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 17:22:29

This is the code iam using and which gives error and usp_issues_dal is the stored procedure iam calling multiple times accoring to the number of rows

declare @List varchar(8000)
select @List = coalesce(@List, '') + 'exec(''usp_issues_dal
''''' + cast( (SELECT order_id FROM tbl_info_master
where tm_id=15) as varchar(50)) + ''''',
''''' + cast(cur_number as varchar(50)) + ''''',
''''' + cast(cur_code as varchar(50)) + ''''',
''''' + cast(cur_description as varchar(7000)) + ''''',
''''' + cast(cur_by as varchar(50)) + +''''');'
from tbl_issues where tm_id=15
exec('''' + @List + '''')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 17:24:15

Instead of your exec statement, do this:

PRINT @List

Post the results here.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 18:11:01

when I use PRINT '''' + @List + '''' instead of exec it shows query bacth completed but nothing else is showing

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 18:14:10

Then @List is probably NULL.

Do this instead of PRINT:

IF @List IS NULL
PRINT 'NULL'
ELSE
PRINT 'Must be something else.'

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 18:16:00

its printing NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 18:17:59

You are probably trying to concatenate a NULL with a string somewhere in there and CONCAT_NULL_YIELDS_NULL is on. So turn it off. Check BOL for details.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-24 : 19:11:48

This is what I get when i set SET CONCAT_NULL_YIELDS_NULL OFF .But still when I try to execute it stills give me error

'exec('usp_issues_dal
''9806'',
'''',
''test'',
''test:werew'',
''manu'');exec('usp_issues_dal
''9806'',
'''',
''FC SR. ITEMS'',
''FC SR. ITEMS:fdfdf'',
''fdfdsf'');'

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources