How to use "and" "or" with if condition in "if statement"

Question

0.00/5 (No votes)

See more:

i am getting error

subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SQL

AS
BEGIN
declare @under varchar(50)

	set @under = (select Group_Name from tbl_AccountGroup where UnderGroup='Sales A/C')
		
		IF @abc='insert' and  (@Group_Name='Sales A/C' or @Group_Name=@under)
	
		BEGIN
			
			

				set nocount on;
				
			DECLARE  @Customer_Id int
			declare @Group_Id int
				 insert into tbl_CustomerCreation select [Group_Id],@Customer_Name,@Account_No,@Email,
			@Billing_Address,@Shipping_Address,@Phone_No,@Fax,@Date,@OB,@Nature1,@CB,@Nature2,@Description from
			tbl_AccountGroup WHERE Group_Name =@Group_Name
			SET @Customer_Id= SCOPE_IDENTITY();
	
			
		END

Posted 30-Mar-16 20:31pm

Member 12385326

Updated 30-Mar-16 23:41pm

Patrice T

v2

Add a Solution

Comments

Patrice T 31-Mar-16 4:32am

Have never think about reading SQL documentation and follows tutos ?

jaket-cp 31-Mar-16 5:03am

execute the following:
select Group_Name from tbl_AccountGroup where UnderGroup='Sales A/C'
what is being returned?

Member 12385326 31-Mar-16 5:24am

it is returning group names which are under Sales A/C group

jaket-cp 31-Mar-16 5:27am

how many?

Member 12385326 31-Mar-16 5:34am

it depends on us..how many we are adding.

jaket-cp 31-Mar-16 5:35am

okay - that will not work

Member 12385326 31-Mar-16 5:39am

is there any other solution i can implement other than than "or"

jaket-cp 31-Mar-16 5:43am

I do not believe the "or" in the if condition is that issue.
I have posted a solution which maybe the cause of the problem.

jaket-cp 31-Mar-16 5:35am

are they the same value?

Member 12385326 31-Mar-16 5:43am

no. suppose Sales A/c is my main group i can have sales1,sals2,sale3
accounts under Sales A/c.they will never ever same.

Member 12385326 31-Mar-16 5:44am

yes. you are write.because i that query is returning more than one value thats why i am getting this error.i just checked

1 solution

Add a Solution

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

jaket-cp · Accepted Answer · 2016-03-30T23:41:00

Solution 1

From the comments, you are attempting to populate

SQL

@under varchar(50)

with multiple values.

This will not work.
I suggest create a variable table and populate it with the Group_Name values (similar to what you have done in another question).

SQL

DECLARE @data TABLE (grp VARCHAR(100));
INSERT INTO @data 
select Group_Name 
from tbl_AccountGroup 
where UnderGroup='Sales A/C'

Use the table to check if @Group_Name exists in the table.

You should be able to figure it out from here.

Posted 30-Mar-16 23:41pm

jaket-cp

Comments

jaket-cp 31-Mar-16 6:33am

okay - how do you know it is not working?

what are the values of @abc and @Group_Name
and the begin for the if is in the wrong place.

Member 12385326 31-Mar-16 6:44am

i have not posted my all code. @abc is variable which is i m using to execute my procedure.if it is @abc='insert' then it will execute insert code.like this i have update,select,delete assigned to @abc .though i am combining insert update.....in one procedure only.and i have "brgin" below my if statement also.

Group names are the names which i am fetching from another table.in that table also i have two columns "group name" and "undergroup"
firstly we add group name by selecting checkbox as it is primary group.for eg. "group name=sales a/c" then on next insert checkbox is unchecked and we add undergroup which is under group name. for eg. "group name=sales 1 and undergroup=sales a/c"

jaket-cp 31-Mar-16 6:48am

But what is the value of @abc and @Group_Name when it gets to the if statement?
For example: when @abc = 'UPDATE' then it will not meet the criteria of the if statement and the insert will not be performed.

Member 12385326 31-Mar-16 6:50am

ASBEGIN
declare @UnderGroup varchar(50)
DECLARE @data TABLE (under VARCHAR(100));
INSERT INTO @data
select Group_Name
from tbl_AccountGroup
where @UnderGroup='Sales A/C'

begin
IF @abc='insert' and ( @Group_Name='Sales A/c' or @Group_Name IN (SELECT under FROM @data ))

BEGIN

--print 'block executed'

set nocount on;
--set @under=(select Group_Name from tbl_AccountGroup where UnderGroup='Sales A/C' group by Group_Name)
DECLARE @Customer_Id int
--declare @Group_Id int
insert into tbl_CustomerCreation select [Group_Id],@Customer_Name,@Account_No,@Email,
@Billing_Address,@Shipping_Address,@Phone_No,@Fax,@Date,@OB,@Nature1,@CB,@Nature2,@Description from
tbl_AccountGroup WHERE Group_Name =@Group_Name
SET @Customer_Id= SCOPE_IDENTITY();

END

see it is like this

jaket-cp 31-Mar-16 6:55am

As i mentioned there is a begin before the if - what is it doing there?

A way of finding out what the values of @abc and @Group_Name are is do a print of them just before the if statement and when you run it should show up in the messages tab (that is if you are using sql server management studio)

Member 12385326 31-Mar-16 7:04am

it is "begin" "As begin " which is below As when we start procedure.

also this code is not working when i tried to get values in table
declare @UnderGroup varchar(50)
DECLARE @data TABLE (under VARCHAR(100));
INSERT INTO @data
select Group_Name
from tbl_AccountGroup
where @UnderGroup='Sales A/C'

select under from @data

jaket-cp 31-Mar-16 7:12am

not the


AS
BEGIN

But the


begin
IF @abc='insert' and ...

The @data table insert and select statement needs changing.
The where clause should be where UnderGroup='Sales A/C'
you have added an extra @, where it is checking if the variable
@UnderGroup is the sales value.

Member 12385326 31-Mar-16 7:16am

thnks a lot for your help

jaket-cp 31-Mar-16 7:19am

No problem glad to help.
Hope you have learnt some more today :)

Member 12385326 31-Mar-16 7:15am

sorry.i was my mistake.code is working the mistake was in where condition "@undergroup" should be "undergroup". so when i select that table i got all the values also it is working in if condition also

jaket-cp 31-Mar-16 7:18am

Good to hear.
We got there eventually :)

How to use "and" "or" with if condition in "if statement"

1 solution

Solution 1

Add your solution here

Preview 0

How to use "and" "or" with if condition in "if statement"

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us