calling sp muliple times using select statements

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)

calling sp muliple times using select statements

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-29 : 21:59:27

I have stored procedure to be fired for 100 emp_id values

usp_emp_details_u 375,1,'Y',null,'EMP-A21',null

select emp_id,emp_checker
from tbl_emp_details
and active_flag = 'Y'
All these values should be updated Here the problem is the first parameter is int So I can get a result like..Lets say I have 5 emp_ids from the result.so the result would be like

usp_emp_details_u 367,1,'Y',null,'EMP-A21',null
usp_emp_details_u 678,1,'Y',null,'EMP-B22',null
usp_emp_details_u 963,1,'Y',null,'EMP-F71',null
usp_emp_details_u 234,1,'Y',null,'EMP-K31',null
usp_emp_details_u 676,1,'Y',null,'EMP-PM1',null

Here 367 those values are emp_ids and 'EMP-A21' values are emp_checker.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-30 : 00:12:38

something like :-

select 'usp_emp_details_u,+ emp_id + ','+ 1 + ',' + ''''+'Y'+'''' +','+ NULL + emp_checker + ','+NULL

But Here it says I can't convert usp_emp_details_u to int because emp_id is int.Please help me out...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-30 : 11:54:10

Its Still Conversion error

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-30 : 11:54:53

ERROR :-
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'execute usp_emp_details_u to a column of data type int.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 01:48:06

Try this

Select 'usp_emp_details_u,'+ cast(emp_id as varchar)+ ','+ cast(1 as varchar) + ',' +
''''+'Y'+'''' +','+ 'NULL,''' + emp_checker + ''','+'NULL' from yourTable

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 02:17:23

You'll probably neeed an EXEC on the front too - i.e.

Select 'EXEC usp_emp_details_u,' ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 02:30:15

Thats Good Point Kris

But I think It will work without Exec also

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 02:32:05

From memory I don't think

usp_emp_details_u 367,1,'Y',null,'EMP-A21',null
usp_emp_details_u 678,1,'Y',null,'EMP-B22',null

will work. It needs a GO in between each one, or an EXEC (if you want them to all be treated as a single batch)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 02:40:32

Yes Thats Correct Kris
If only one sp to be run at a time, then there is no need of Go
I thought he/she will run it seperately(If there are many emp_ids and running them in a single batch can cuase performance issues)

Madhivanan

Failing to plan is Planning to fail

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-08-31 : 02:49:42

If possible (basing on what is done in the proc), I'd rather convert that proc to a function (UDF).

but first, look up BOL for the limitations on using functions..

Hemanth Gorijala
I Came. I Saw. I Normalized.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-31 : 14:56:43

IT works Thanks a lot.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources