how to do this? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

how to do this?

Author

Topic

codrgi
Starting Member

11 Posts

Posted - 2009-04-22 : 22:04:45

i want to exec a stored procedure with the parameter thats inside a table somewhere, say i do this

select @col = column from table
    exec storedprocedure @col

i want it to do the stored precedure for the whole table finding the parameter itself by using the select statement, not just the last row it finds, as when i do this it only does it for just one row, how do i go about doing this for each row it finds, that i ask it to select

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-22 : 22:15:33

You may want to look into user defined functions instead of stored procedure for this. Can you give us some more details of what type of logic is in the stored proc?

codrgi
Starting Member

11 Posts

Posted - 2009-04-22 : 22:39:22

the code i posted is just one procedure i made so that i dont have to put the parameter myself as theres alot of parameters that needs done, this procedure just makes it pick out the parameter of the next procedure so i dont have to do it manually as theres over 600 rows, this would just make it much easier, i tried the select query above but it only uses the last row it finds as one parameter

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-22 : 23:29:19

If the procedure youre calling is acting on a table (update, delete, etc) then Im sure we could help you write a set based statement to modify it based on the rows of another table. It would be much more efficient.

To just answer your question, to loop through you could do something like this:


declare @Process table (i int identity(1,1), Col int)
insert into @Process (Col)
	select Col from table

declare @i int

select @i = MIN(i) from @Process

while @i is not null
begin
		select @col = Col form @Process where i = @i 
		exec storedprocedure @col
		
		select @i = MIN(i) from @Process where i > @i

end

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-22 : 23:36:05

though, i suspect youre trying to solve a problem here with procedural logic (row by row) rather than applying a set based solution (sqlteam style).

If youre interested, please post more details about the underlying tables and some sample data.

Thanks!

codrgi
Starting Member

11 Posts

Posted - 2009-04-23 : 00:00:30

i tried it and didnt work, all i want to to is select a column from a table and let the procedure pick out its own parameters from that column moving down the column itself, after each one has finished

i tried this, but it only selects the very last column of the table missing the other rows even though, i'm using the select column from table statement which i believe selects everything, and dont understand why it only selects the very last column of the table

declare @col int


begin
		select @col = column from thetable 
		exec storedprocedure @col

end

i appreciate your help on this one

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-23 : 00:04:51

It actually selects the values from each and every row, but only the last value (the very last column as you put it) is in the parameter when you exec the procedure.

Try my example

codrgi
Starting Member

11 Posts

Posted - 2009-04-23 : 00:45:46

i used this



declare @Process table (i int identity(1,1), strticket int)
insert into @Process (strticket)
	select strticket from reports

declare @i int,@col int

select @i = MIN(i) from @Process

while @i is not null
begin
		select @col = strticket from @Process where i = @i 
		exec ticketfind @col
		select @i = MIN(i) from @Process where i > @i

end

codrgi
Starting Member

11 Posts

Posted - 2009-04-23 : 12:40:14

it dosent use the right parameters, it entered the columns rows to whatever table and was touching stuff it shouldnt have, is there any other way to do this?

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-23 : 13:11:25

It will execute your procedure once per row in @Process (once for every row you insert from reports table). If those are not the right params that modify the insert into @Process.

I have no idea what you mean by "touching stuff."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources