whats wrong with this sp - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

whats wrong with this sp

Author

Topic

rdz555
Starting Member

6 Posts

Posted - 2009-06-10 : 17:38:50

create proc parentesco2
@a char(1),
@b char (1)

as
begin
declare @pa char(1)
declare @pb char(1)
declare @res varchar (50)
declare @res2 varchar(50)
select @pa=sujeto from persona where sujeto =@a
select @pb=sujeto from persona where sujeto =@b
set @res=
case
when @pa = @b then 'es hijo de'
when @pb = @a then 'es padre de'
when @pa = @pb then 'es hermano de'
else 'otro'
end
If @res is Null
If @pa is Null and @pb is Null
Set @res = 'otro'
Else
Begin
If @pa is Not Null
Set @Res = (@pa, @b)
If @pb is Not Null
Set @Res2 = @a, @pb
If @Res <> 'otro'
Set @res = Case @res When 'hermano' Then 'sobrino' Else 'nieto'
End
Else if @Res2 <> 'otro'
Set @res = Case @res2 When 'hermano' Then 'tio' Else 'abuelo'
end

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-10 : 17:43:44

I don't know, you tell us? Is it throwing you an error message? Is it giving you results you're not expecting? You can't just post your sp and expect us to know what YOU want it to do.

Some days you're the dog, and some days you're the fire hydrant.

rdz555
Starting Member

6 Posts

Posted - 2009-06-10 : 17:52:44

yes is throwing an error message

is the sintaxis correct?
thank you

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-10 : 18:00:13

When you encounter error (in mgmt studio) you can double click the error message and your cursor will jump to the area of the error. Its not always 100% accurate, but it gets you close.

In this case, mgmt studio is right on.

Do you see anything wrong with these lines of your procedure:

Set @Res = (@pa, @b)
If @pb is Not Null
Set @Res2 = @a, @pb

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-10 : 18:04:01

And once you clear those errors repeat the process over to clear any errors that surface

rdz555
Starting Member

6 Posts

Posted - 2009-06-10 : 18:15:16

can you tell me how it should be

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-10 : 18:41:09

Ok, lets take the first 2 errors:

Set @Res = (@pa, @b)

and

Set @Res2 = @a, @pb

You cannot set the parameter @Res equal to more than one value (ie, @pa and @b) in this way. This syntax is invalid. What do you want to do in this line of code? If you want to set it equal to the concatenation of both parameters then you need:

Set @Res = @pa + @b

If you want to set it equal to the first non-null value within the set then you need:

Set @Res = coalesce(@pa, @b)

Please be more specific with what you are trying to accomplish.

rdz555
Starting Member

6 Posts

Posted - 2009-06-10 : 18:44:01

sorry
im trying to exec other sp

like this
create proc parentesco2
@a char(1),
@b char (1)

as
begin
declare @pa char(1)
declare @pb char(1)
declare @res varchar (50)
declare @res2 varchar(50)
select @pa=padre from persona where sujeto =@a
select @pb=padre from persona where sujeto =@b
set @res=
case
when @pa = @b then 'es hijo de'
when @pb = @a then 'es padre de'
when @pa = @pb then 'es hermano de'
else 'otro'
end
Begin
If @pa is Not Null
Set @Res = exec parentesco @pa, @b
If @pb is Not Null
Set @Res2 = exec parentesco @a, @pb
If @Res <> 'otro'
Set @res = Case @res When 'hermano' Then 'sobrino' Else 'nieto'
End
Else if @Res2 <> 'otro'
Set @res = Case @res2 When 'hermano' Then 'tio' Else 'abuelo'
end

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-10 : 18:56:42

If the 2nd proc is returning data via an output param then the syntax is: