SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Exec @ret_status From SQL Select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vcs1161
Starting Member

10 Posts

Posted - 07/31/2013 :  10:33:26  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/31/2013 :  10:40:49  Show Profile  Reply with Quote
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
Go to Top of Page

vcs1161
Starting Member

10 Posts

Posted - 07/31/2013 :  11:13:10  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/01/2013 :  02:37:44  Show Profile  Reply with Quote
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
Go to Top of Page

vcs1161
Starting Member

10 Posts

Posted - 08/02/2013 :  11:59:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/03/2013 :  03:20:40  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000