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.
| 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
|
|
|
|
|
|
|
|
|
Exec @ret_status From SQL Select