Exec @ret_status From SQL Select

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)

Exec @ret_status From SQL Select

Author

Topic

vcs1161
Starting Member

10 Posts

Posted - 2013-07-31 : 10:33:26

What is the proper format and syntax to execute a stored procedure based on the certain values you get from an SQL select?

So if my select was:

Select 
ValueA,
ValueB
From
table1
Where 
Date between Date1 and Date2

And there is as stored procedure that will use the ValueA and ValueB as the parameters from a Stored Procedure (will call it SP1) to process output values.

Where and how would I incorporate something like this in my SQL:

exec @ret_status = SP1
ValueA,
ValueB,
@value1 output,
@value2 output,
@value3 output

I would assume I need to declare ValueA and ValueB as variables in my select first then have each variable value process through the stored procedure to get my output values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 10:40:49

yes you're right

something like


declare @valA datatype, @valB datatype

Select 
@valA = ValueA,
@valB = ValueB
From
table1
Where 
Date between Date1 and Date2


exec @ret_status = SP1
@ValA,
@ValB,
@value1 output,
@value2 output,
@value3 output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vcs1161
Starting Member

10 Posts

Posted - 2013-07-31 : 11:13:10

I am getting there. Now I would like to insert the values into a temp table for each record until it has scanned through all records. Do I do an Insert into #temp_table values(@valA, @valB,@value1 ,@value2,@value3)?

I only get one record line when I try that. So what is the proper syntax to build all the values into this table as a full result set?

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 02:37:44

quote:
Originally posted by vcs1161

I am getting there. Now I would like to insert the values into a temp table for each record until it has scanned through all records. Do I do an Insert into #temp_table values(@valA, @valB,@value1 ,@value2,@value3)?

I only get one record line when I try that. So what is the proper syntax to build all the values into this table as a full result set?

Thank you.

you need a loop for that. if you want to call procedure for each record in table you need to do it in a loop.

If you want return values to be grabbed as a resultset you can use a table to store the result.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vcs1161
Starting Member

10 Posts

Posted - 2013-08-02 : 11:59:17

The Stored Procedure relies on @ValueA and @ValueB to receive the output results. Here is the format and approach I have started but not getting it to read the output values correctly.

declare
@ValueA int,
@ValueB int,
@value1 varchar(10),
@value2 int,
@value3 varchar(10)

create table #temp_table
(
tmp_ValueA int null,
tmp_ValueB int null,
tmp_value1 varchar(10) null,
tmp_value2 int null,
tmp_value3 varchar(10) null
)
set rowcount 0
insert #temp_table
select distinct
ValueA,
ValueB,
Null,
Null,
null
From
Table1
where
Date between '06/4/2013' and '06/05/2013'

set rowcount 1

while @@rowcount > 0
begin

exec SP1
@ValueA,
@ValueB,
@value1 output,
@value2 output,
@value3 output

if @value1 in ('Go', 'Pass')
begin
if @value2 = 1
select @value3 = 'ON'
else
select @value3 = 'OFF'

end
set rowcount 0
select * from #temp_table where tmp_ValueA = @ValueA and tmp_ValueB = @ValueB
delete #temp_table where tmp_ValueA = @ValueA and tmp_ValueB = @ValueB
set rowcount 1
update #temp_table
set
tmp_value1 = @value1,
tmp_value2 = @value2,
tmp_value3 = @value3
end
set rowcount 0

select * from #temp_table

drop table #temp_table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 03:20:40

you're not assigning the values of @ValueA and @ValueB before passing them to stored procedure


declare 
@ValueA int, 
@ValueB int,
@value1 varchar(10),
@value2 int,
@value3 varchar(10)

create table #temp_table
(
tmp_ValueA int null,
tmp_ValueB int null,
tmp_value1 varchar(10) null,
tmp_value2 int null,
tmp_value3 varchar(10) null
)
set rowcount 0
insert #temp_table
select distinct
ValueA,
ValueB,
Null,
Null,
null
From
Table1
where
Date between '06/4/2013' and '06/05/2013'

select top 1 @ValueA=tmp_ValueA ,
@ValueB= tmp_ValueB
from #temp_table
order by tmp_ValuA


while @ValueA is not null
begin

exec SP1 
@ValueA, 
@ValueB,
@value1 output,
@value2 output,
@value3 output

if @value1 in ('Go', 'Pass')
begin
if @value2 = 1
select @value3 = 'ON'
else
select @value3 = 'OFF'

end

select * from #temp_table where tmp_ValueA = @ValueA and tmp_ValueB = @ValueB
delete #temp_table where tmp_ValueA = @ValueA and tmp_ValueB = @ValueB

update #temp_table
set 
tmp_value1 = @value1,
tmp_value2 = @value2,
tmp_value3 = @value3


select top 1 @ValueA=tmp_ValueA ,
@ValueB= tmp_ValueB
from #temp_table
where tmp_ValueA > @ValueA
end


select * from #temp_table

drop table #temp_table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources