Stored Proc class within statments

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

SQL Server Development (2000)

Stored Proc class within statments

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-28 : 20:20:00

Hey all,

just wondering if there is any way to call a stored procedure from within a select insert update or delete statment?

using the values as paramters and withouth using temp tables

for example
from this
select myrow from mytable
want to use the myrow value as parm in stored procedure
SELECT exec myproc MYROW FROM MYTABLE

or in a update

update mytable
set myrow = exec myproc

any ideas?

i know i can use functions? but are there limitations on functions vs stored procedures? like can they still insert update delete etc? cause the myproc would need to do that as well?

this is all to avoid using a cursor that has a stored procedure call within it using the row by row values as paramters

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 20:22:05

insert - yes
insert tbl exec myproc

select / update
only using openrowset / openquery which will execute the sp on another connection and you probably don't want that.

You can use a udf though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-28 : 20:46:12

ok question along a smilar line might help my problem

is there any way to do something like this
pass a table into a stored procedure or a result set

exec bb (select id2 from emp where title ='b')

/*SP*/
create procedure bb (@id2 table)
as
update emp
set name ='yay' where id2 = @id2
/*OR*/
create procedure bb (@id2 int)
as
update emp
set name ='yay' where id2 = @id2

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 20:58:36

select id2 into #tbl from emp where title ='b'
exec bb

create procedure bb
as
update emp
set name ='yay' where id2 = in (select id2 from #tbl)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources