getting error for if/else in a stored procedure

Question

I am relatively very new to SQL queries but I have this stored procedure where I am trying to get value of a declared variable as mentioned below but getting error,

First line is 20 here,

declare @m_ID_v int set @m_ID_v = ( select ID_C from M_T where MName_C = @MName_parameter) declare @g bit if (select G_L_Column from G_L_table Where M_ID_Column = @M_ID_variable) set @g_v = 1 else set @g_variable = 0

Exception I get:

Msg 4145, Level 15, State 1, Procedure GetID, Line 20
An expression of non-boolean type specified in a context where a condition is expected, near 'set'. Msg 156, Level 15, State 1, Procedure GetID, Line 21 Incorrect syntax near the keyword 'else'.

Now if I remove declare @g... and try to parse it, no error occurs

EDIT

I want my code to check for returned value by my select statement so "if exists" is not really what am looking for, sorry.

You've defined a variable called @g but the assignment goes to a variable called @g_v or @g_variable ..... — marc_s
– marc_s, Commented Oct 15, 2012 at 9:21
sorry I changed there names because of company code privacy, sorry, but thanks for correcting me as well :) — Change
– Change, Commented Oct 15, 2012 at 9:22
When you say you want to check the returned value, what do you want to check it against? Do you mean IF ((SELECT x FROM y WHERE z = @v) = @testVariable)? At present you get back G_L_Column, but you don't say what you want to check it against. Also, is M_ID_Column unique, allowing you to guarantee that your query only ever returns one row? — MatBailie
– MatBailie, Commented Oct 15, 2012 at 9:36
yup it is unique I am a c# programmer and my SQL statement was returning true or false so I thought SQL will check result itself but not really however now its working, thanks :) — Change
– Change, Commented Oct 15, 2012 at 9:40

Habibillah · Accepted Answer · 2012-10-15 08:57:03Z

2

try use if exists:

declare @g_v bit if exists(select G_L_Column from G_L_table Where M_ID_Column = @M_ID_variable) set @g_v = 1 else set @g_v = 0

answered Oct 15, 2012 at 8:57

Habibillah

28.8k5 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Change Over a year ago

it did worked and I will accept your answer in 8 minutes but can you also give a bit explanation as well please and if what am doing is best practice or not :)

Habibillah Over a year ago

@Ignacio, It's the common way to check the query return one or more result. For another case, you may put it as sub query or joining table for avoid if statement and exists keyword.

Change Over a year ago

sorry I am not looking for "if exists" as it won't gonna work with my logic

Vikram Jain · Accepted Answer · 2012-10-15 09:02:03Z

declare @m_ID_v int set @m_ID_v = ( select ID_C from M_T where MName_C = @MName_parameter) declare @g bit if ((select G_L_Column from G_L_table Where M_ID_Column = @M_ID_variable) = value ) set @g_v = 1 else set @g_variable = 0

podiluska · Accepted Answer · 2012-10-15 08:59:28Z

1

You can't say

 if (select ...

You have to compare something with something else in a if statement, or use a boolean function such as exists

answered Oct 15, 2012 at 8:59

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

1 Comment

Change Over a year ago

cheers, I would give you +1 if I had enough reputation, thanks anyway

Collectives™ on Stack Overflow

getting error for if/else in a stored procedure

3 Answers 3

3 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Linked

Related