How to put the result of a stored procedure in a condition in the select statement?

Question

0.00/5 (No votes)

See more:

So i'm inserting multiple records in one select statement and in one of the conditions (condition2) I want to somehow say (if the subtraction of the 2 results sets that are returned from the stored procedure = 0) is that doable?

Insert into Table1 select *from table2 where condition1 and condition2

my stored procedure has 2 select statements where each one returns an integer

so i want to copy all records from table2 to table1 where condition1 matches it and if ( for each record in table 2 where result1 - result2 = 0) then all these records should be copied to table 1

What I have tried:

I don't think i can re-write the two select statements in my insert statement right)? so is what i want to do doable for inserting multiple records in one insert select statement? or should i insert each record one by one to be able to do it?

Posted 9-Aug-20 0:00am

Member 14800672

Updated 9-Aug-20 1:00am

v4

Add a Solution

Comments

Wendelius 9-Aug-20 6:06am

It would be easier to solve the problem if you would post the whole statements you're trying to use.

Member 14800672 9-Aug-20 6:25am

Here's my stored procedure
(
select field1 *from table2 where Id =@Id -- result 1
select count(field1) from table2 where Id=@Id and Id2= @Id1 -- result 2
)

So now i want to say in a new stored procedure
Insert into table1 select from table2 where Id=@Id and Id2= @Id1 and (result 1 - result 2=0)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2020-08-09T01:00:00

Not sure if I fully understand the situation but one option is to use functions instead of stored procedure. Creating individual functions to fetch the values would help you to return data that can be used in a condition.

Consider the following example
(however, keep in mind that functions used in a condition typically have a negative performance impact if the amount of data is large)

SQL

-- test table 1
create table t1 (
 id int,
 val int
);

insert into t1 values
(11,11),
(12,12),
(13,13);

-- test table 2
create table t2 (
 id1 int,
 id2 int,
 val int
);

insert into t2 values
(11,21,1),
(12,22,2),
(13,23,3);

-- test table 3
create table t3 (
 id int,
 val int
);

insert into t3 values
(1,1),
(1,2),
(2,1),
(2,2),
(3,1),
(3,2);

-- functions
create function f1 (@id int) returns int as
begin
   declare @retvalue int;
   select @retvalue = t1.val from t1 where t1.id = @id;
   return (@retvalue);
end;

select dbo.f1(11); -- returns 11

create function f2 (@id1 int, @id2 int) returns int as
begin
   declare @retvalue int;
   select @retvalue = t2.val from t2 where t2.id1 = @id1 and t2.id2 = @id2;
   return (@retvalue);
end;

select dbo.f2(dbo.f1(11), 21); -- returns 1

-- actual query to use functions in a condition
select * from t3 where t3.id = dbo.f2(dbo.f1(11), 21);

-- returns 
--
-- id   val
-- --   ---
-- 1    1
-- 1    2