Calling an SP in an SP problem

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 2000 Forums

SQL Server Development (2000)

Calling an SP in an SP problem

Author

Topic

jweizman
Starting Member

27 Posts

Posted - 2002-08-27 : 07:24:38

Hi

I have a stored procedure that insert in an Activity table some info.
In this case when the user searches for someone, i call a sp called
spIActivity that inserts the searched string.

I want to concatenate the searched Lastname+firstname+MiddleName+... and write this string the Activity table.

This is the code. It does not work.

Now if i replace the corresponding lines by

--select @Description = 'LN :' + @Lastname + 'FS :' + @Firstname + 'MN :' + @MiddleName + 'DOB1 :' + @DateofBirth

exec spIActivity 'Search','Person',@userid,@Belongs2Group,''

Then at least it writes null in the corresponding field but with the code below (select @Description... uncommented) it even does not execute the spIActivity SP

CREATE procedure spSelectPerson
@PersonID nvarchar(20) = null,
@Lastname nvarchar(20) = null,
@Firstname nvarchar(30) = null,
@MiddleName varchar (30) =null,
@DateofBirth smalldatetime =null,
@ReturnCode int = 0 output,
@RowCount int = 0 output
as

set nocount on

Declare @Description nvarchar(255)

select * from Persons
where
(((@PersonID is null or PersonID = @PersonID) AND
(@Lastname is null or Lastname like @Lastname +'%') AND
(@Firstname is null or Firstname like @Firstname+'%') AND
(@MiddleName is null or MiddleName like @MiddleName+ '%')) OR
(@DateofBirth is null or DateofBirth like @DateofBirth) AND

select @ReturnCode = @@error, @RowCount = @@rowcount

select @Description = 'LN :' + @Lastname + 'FS :' + @Firstname + 'MN :' + @MiddleName + 'DOB1 :' + @DateofBirth

exec spIActivity 'Search','Person',@userid,@Belongs2Group,@Description

return @ReturnCode
GO

Any help ?

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-08-27 : 08:55:15

It seems that the problem is with your called SP spIActivity.
Print all the parameters that are passed to the second procedure and then
Execute your spIActivity with these parameters and see if that is successful.
You can also check the return value of proc. If return value is 0 then no error else debug your called SP.

Ramesh

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-27 : 16:03:19

This may be a problem with NULLs. If any of the values in your string:
select @Description = 'LN :' + @Lastname + 'FS :' + @Firstname + 'MN :' + @MiddleName + 'DOB1 :' + @DateofBirth

is null then the whole string will return Null. You may need to look up ISNULL() or some similar function.

Edited by - ajarnmark on 08/27/2002 16:04:07

jweizman
Starting Member

27 Posts

Posted - 2002-08-27 : 18:40:07

Thanks for this last answer

But it seems that even with one value (not null) it does not work

Is there a way to display the @description variable

Also where in the SQL doc can i find theis issue of the null value in the concatenated string ?

Thanks very much

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 18:52:19

select @Description should do it.
Can also create a table and insert into it.

look in bol under null concatenation.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-08-27 : 19:39:09

Look in BOL for ANSI NULL

Specifically..

SET CONCAT_NULL_YIELDS_NULL OFF

HTH

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources