March 5, 2008 at 2:26 pm
i'm trying to create a conditional sproc that evaluates an input parameter and returns different output depending on that parameter value.
I have:
create procedure sp_foo
@inputval varchar
as
if @inputval <> 'ALL'
select * from tableA where someval=@inputval
else
select * from tableB
but when i run sp_foo as
exec sp_foo 'ALL'
I get the results from table A. I'm pretty sure this is a syntax problem.
March 5, 2008 at 2:34 pm
(if <> ) like else
try it this way
alter procedure sp_foo
@inputval varchar
as
begin
if @inputval like 'ALL'
begin
print 'ALL'
end
else
print 'hi'
end
..>>..
MobashA
March 5, 2008 at 2:37 pm
You defined the parameter as VARCHAR without specifying a length. If no length is specified, it it 1 character long, essentially VARCHAR(1)
So at the point of the if, @inputval = 'A' because the rest of the passed in value was truncated and lost.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2008 at 2:43 pm
Thanks Gila - didn't know that varchar would drop to length of 1. That's stupid! But good to know...Knowing that, I could fix it no problem.
March 5, 2008 at 2:46 pm
in ur case the first condition is always true.
cos u r comparing A with ALL
..>>..
MobashA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply