select variable - = result of stored procedure

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)

select variable - = result of stored procedure

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-29 : 05:00:06

select @av=getavailablebalance @userid

this errors = what's the right way of doing it - i want av to be equal to the result of the stored procedure getavailablebalance passing in @userid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:04:17

How do the resultset returning from your stored procedure look like?

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-29 : 05:08:10

exec getavailablebalance @userid=6
will return 1 field called availablebalance

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-29 : 05:22:41

thanks :)

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-29 : 05:26:35

Is it working fine esthea?

Avantha Siriwardana

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:27:49

quote:
Originally posted by AvanthaSiriwardana

DECLARE VARCHAR(20)
DECLARE @userid VARCHAR(20)
SET @userid = 6

EXECUTE @av = getavailablebalance @userid
SELECT @av

No no no..
That will give you the RETURN code from stored procedure which is 0 if stored procedure is executed without errors.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:30:22

[code]CREATE PROCEDURE dbo.spGetAvailableBalance
(
@UserID INT
)
AS

SET NOCOUNT ON

SELECT 55000 AS Balance
GO

EXEC dbo.spGetAvailableBalance 5

DECLARE @rc INT

EXEC @rc = dbo.spGetAvailableBalance 5

SELECT @rc AS [@rc]

DROP PROCEDURE dbo.spGetAvailableBalance[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:31:51

This is how you can make it

CREATE PROCEDURE dbo.spGetAvailableBalance
(
	@UserID INT
)
AS

SET NOCOUNT ON

SELECT	55000 AS Balance
GO

DECLARE	@Balance TABLE
	(
		Value INT
	)

INSERT	 @Balance
EXEC	dbo.spGetAvailableBalance 5

DECLARE	@rc INT

SELECT	@rc = Value
FROM	@Balance

SELECT	@rc AS [@rc]

DROP PROCEDURE dbo.spGetAvailableBalance

But you probably would be better off using a user defined function.

SELECT	dbo.fnGetAvailableBalance(6)

E 12°55'05.63"
N 56°04'39.26"

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-29 : 05:32:47

Sorry abt that..
what it is the out put of his/her SP peso..i mean is it a single value??

Avantha Siriwardana

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-29 : 05:44:41

Try this'
DECLARE @ReturnValue int, @OutTest int
EXEC @ReturnValue=SP_name @OutTest OUTPUT
SELECT @ReturnValue AS ReturnValue, @OutTest AS OutTest

Avantha Siriwardana

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-29 : 05:45:06

Hi Peso...

Is the below query right if the sp returns a single value ??( Please ignore the datatype diff ( That is Availablebalance may be not Varchar))

DECLARE @AV varchar(100)
select @av =
'exec getavailablebalance @userid=6'

exec ( @av )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:06:08

Did you try? What was the result?
I believe you executed the stored procedure but did not assign the returning resultset to a variable.

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-29 : 06:31:20

Yes I executed the procedure and i got the result as 55000 under balance column

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 06:34:28

And assigned it to a variable?

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-29 : 08:03:24

declare @av money
EXEC @av = getAvailableBalance 7
sELECT @av AS [@av]

this is showing the available balance but setting av =0

raky above didn't work either.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 08:10:52

quote:
No no no..
That will give you the RETURN code from stored procedure which is 0 if stored procedure is executed without errors.

See my two posts made
01/29/2009 : 05:30:22 -- Proof that code is wrong
01/29/2009 : 05:31:51 -- Code to make things right

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-29 : 08:19:32

can you help me with how i would make it a user defined function?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 08:22:01

Can I see the code for the stored procedure?

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-01-29 : 08:30:06

ALTER PROCEDURE [dbo].[getavailablebalance]
-- Add the parameters for the stored procedure here
@userid int

AS
declare
@paymentamount money,
@potentialtolose money
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @paymentamount=sum(paymentamount) from userpayments where userid=@userid
--select @potentialtolose=0
select @potentialtolose= ISNULL(sum(potentialtoloseamount), 0.00) from useritemsbought where userid=@userid and closeddate is null

select @potentialtolose=@potentialtolose + ISNULL(sum(potentialtoloseamount), 0.00) from useritemssold where userid=@userid and closeddate is null
select @paymentamount-@potentialtolose as 'availablebalance'
END
GO

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-29 : 09:01:51

Peso is right, a function would probably be better. However, you can use an output parameter in the stored procedure as well:


ALTER PROCEDURE [dbo].[getavailablebalance] 
-- Add the parameters for the stored procedure here
	@userid int,
	@available_balance money OUTPUT
AS
DECLARE @paymentamount money,
		@potentialtolose money
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT @paymentamount=sum(paymentamount) from userpayments where userid=@userid
	--select @potentialtolose=0
	select @potentialtolose= ISNULL(sum(potentialtoloseamount), 0.00) from useritemsbought where userid=@userid and closeddate is null

	select @potentialtolose=@potentialtolose + ISNULL(sum(potentialtoloseamount), 0.00) from useritemssold where userid=@userid and closeddate is null
	select @available_balance = @paymentamount-@potentialtolose
END
GO
 

-- Execute stored procedure
DECLARE @userid int
DECLARE @available_balance money

-- TODO: Set parameter values here.
SET @userid = 7
EXECUTE [dbo].[getavailablebalance] 
   @userid
  ,@available_balance OUTPUT

SELECT @available_balance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:03:43

which value are you trying to return from procedure?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources