output paramater to not return to parernt sp

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)

output paramater to not return to parernt sp

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 06:28:32

i have an sp which calls another sp with an output variable to return

now this sp returns a result and I don't want it to return the parameter as a separate result -- i want it to return 1 recordset

how can i do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 06:33:34

You will have to modify that SP to do that.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 06:37:56

how?

it needs to return to the parent sp the output but I don't want the parent sp to write it out?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 06:40:21

it will be easier if you can post the code of the 2 SP here

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 06:46:29

ALTER PROCEDURE [dbo].[spprint]
@id int
AS
declare @p varchar(50)
SET NOCOUNT ON;
WHILE @p IS NULL
BEGIN
exec spnewp @p output
IF EXISTS (SELECT * FROM customers WHERE p = @p)
SET @p = NULL --Not unique
END

select * from customers where id=@id
return
GO

/*ideally i'd like to return all fields from customer and the p but in the same recordset not 2 different ones as it is doing now */

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 07:49:52

Can you explain what the spprint and spnewp is doing ?

Also show what result set the spprint is currently returning and how would like it to return ?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 07:55:13

spprint returns the record

id,firstname,lastname, and all field in teable

spnewp return the p -- a 7 digit varchar

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-11 : 08:10:59

use a function for spnewp, then you can call it inline

--------------------
keeping it simple...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 08:25:58

do you mind telling me how to do this?

the sp for spnewp is

CREATE PROCEDURE [dbo].[spnewp]
-- Add the parameters for the stored procedure here
(@p int output)
AS

BEGIN

SET NOCOUNT ON;

set @p=cast(round((8999998 * Rand() + 1000000), 0) as int)
select @p as [p]

end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-11 : 08:31:00

You can't create function for that since functions does not allow non-deterministic system functions like RAND() to be part of UDFs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 08:33:39

so is there anyway to do this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-11 : 08:40:19

You have two ways:

1. Add output parameter to spprint stored proc which will return the value of P in addition to the result set.

2. Add @p as a part of SELECT statement:

select @p as P, * from customers where id=@id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-11 : 08:49:13

i tried number 2 but it still returns both recordsets

just with p and customers with p

i don't knwo what you mean by #1 can you explain?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-11 : 08:54:03

Your primary problem is the select statement in the spnewp stored proce.

Just comment the line striked out below and you won't need any changes to main SP:

CREATE PROCEDURE [dbo].[spnewp] 
-- Add the parameters for the stored procedure here
(@p int output)
AS

BEGIN

SET NOCOUNT ON;

set @p=cast(round((8999998 * Rand() + 1000000), 0) as int)
select @p as [p]
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 09:42:27

quote:
ideally i'd like to return all fields from customer and the p but in the same recordset not 2 different ones as it is doing now

Also change this.


ALTER PROCEDURE [dbo].[spprint]
@id int
AS
declare @p varchar(50)
SET NOCOUNT ON;
WHILE @p IS NULL
BEGIN
exec spnewp @p output
IF EXISTS (SELECT * FROM customers WHERE p = @p)
SET @p = NULL --Not unique
END

select *, @p as [p] from customers where id=@id
return
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources