OUTPUT Parameter question - 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)

OUTPUT Parameter question

Author

Topic

learner_shikha
Starting Member

4 Posts

Posted - 2010-11-25 : 15:00:28

I am running an sp :

********
IF EXISTS (SELECT Name FROM sysobjects WHERE Name = 'spGetBPAccountSalesRep' AND type = 'P')
DROP PROCEDURE spGetBPAccountSalesRep
GO

CREATE PROCEDURE [dbo].[spGetBPAccountSalesRep]
@AccountNumber varchar(50),
@SalesRepNumber varchar(50) OUTPUT,
@FirstName nvarchar(50) OUTPUT,
@LastName nvarchar(50) OUTPUT,
@Email varchar(50) OUTPUT,
@PhoneNumber varchar(50) OUTPUT,
@PreviewImage varchar(1000) OUTPUT,
@CountryCode char(2) OUTPUT
AS
SET NOCOUNT ON

---Local variable declared
DECLARE @ROWCOUNT int

SET @RowCount = 0

IF @AccountNumber IS NOT NULL

BEGIN

---When the sales rep is a Account Manager0
SELECT
@SalesRepNumber = SalesRepNumber,
@FirstName = FirstName,
@LastName = LastName,
@Email = Email,
@PhoneNumber = PhoneNumber,
@PreviewImage = PreviewImage,
@CountryCode = BusinessPartnerSalesRep.CountryCode
FROM dbo.BusinessPartnerSalesRep,
dbo.BusinessPartnerAccountSalesRep,
dbo.BusinessPartnerAccount
WHERE BusinessPartnerSalesRep.BusinessPartnerSalesRepID = BusinessPartnerAccountSalesRep.BusinessPartnerSalesRepID
AND BusinessPartnerAccountSalesRep.BusinessPartnerAccountID = BusinessPartnerAccount.BusinessPartnerAccountID
AND BusinessPartnerAccountSalesRep.SalesRepRole = '002'
AND BusinessPartnerAccount.AccountNumber = @AccountNumber

SELECT @ROWCOUNT = @@ROWCOUNT

---When the Sales Rep is a Default Account Manager
IF @RowCount <> 1
SELECT
@SalesRepNumber = SalesRepNumber,
@FirstName = FirstName,
@LastName = LastName,
@Email = Email,
@PhoneNumber = PhoneNumber,
@PreviewImage = PreviewImage,
@CountryCode = BusinessPartnerSalesRep.CountryCode
FROM dbo.BusinessPartnerSalesRep,
dbo.BusinessPartnerAccountSalesRep,
dbo.BusinessPartnerAccount
WHERE BusinessPartnerSalesRep.BusinessPartnerSalesRepID = BusinessPartnerAccountSalesRep.BusinessPartnerSalesRepID
AND BusinessPartnerAccountSalesRep.BusinessPartnerAccountID = BusinessPartnerAccount.BusinessPartnerAccountID
AND BusinessPartnerAccountSalesRep.SalesRepRole = '001'
AND BusinessPartnerAccount.AccountNumber = @AccountNumber

END

SET NOCOUNT OFF
GO
********

When there is no SalesRep info for an account it retuns NULLs as outputs, when i run this test script:

---Executes the spGetBPAccountSalesRep when the correct input values are passed.

DECLARE @out1 varchar(50)
DECLARE @out2 nvarchar(50)
DECLARE @out3 nvarchar(50)
DECLARE @out4 varchar(50)
DECLARE @out5 varchar(50)
DECLARE @out6 varchar(50)
DECLARE @out7 char(2)

exec spGetBPAccountSalesRep @AccountNumber ='015026001' ,
@SalesRepNumber = @out1 OUTPUT ,
@FirstName = @out2 OUTPUT ,
@LastName = @out3 OUTPUT ,
@Email = @out4 OUTPUT ,
@PhoneNumber = @out5 OUTPUT ,
@PreviewImage = @out6 OUTPUT ,
@CountryCode = @out7 OUTPUT

SELECT
@out1,
@out2,
@out3,
@out4,
@out5,
@out6,
@out7
Go

***
Is there a way i dont see any row when the sales rep info is not existing for an account. The issue is this result set is being passed to another application. If there is no row returned then
in the applicaon code they set default values.

Can some one please help me in finding if there is a way to get no rows returned(not even nulls) when ther eis no sales rep info for a passed in account number.

Please guide!!!!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-25 : 19:25:19

Would seem better suited for a table function, and if @@Rowcount is zero, it would return zero rows. That way you don't need to worry about the outputs....and would be a bit simpler. Then the call would simply be Select * FROM dbo.[tablefunction] (@accountnumber)

Poor planning on your part does not constitute an emergency on my part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-25 : 22:54:59

I would not use the output variables, but I would use a stored procedure to return the results over a table function (personal preferance of I do not like using table variables unless I will get a better performance, also you can access them from a front end application easier and can maintain security easier if you can keep all access to the db from users limited to stored procedures (Again just a personal preferance though.))

The code in the procedure can likely be better written, so if you can post some sample data for the tables and expected output, someone could help you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-25 : 22:55:00

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources