troubleshooting Question

MS SQL Switch Return Value off

Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
4 Comments1 Solution621 ViewsLast Modified:
Hello,

In my SP how do I stop the results creating a Return Value output? I'm trying to use the SP with an ASP VBScript command, but the SP below results in both the results and the Return Value, my command is unable to pick up the data requited.

What do i need to do to resolve this?

Thanks

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ANewRegistration]
 @siteID int,
 @companyname NVARCHAR(50),
 @address NVARCHAR(1000),
 @phone NVARCHAR(50),
 @url NVARCHAR(225),
 @companytype CHAR(1),
 @AccountType NVARCHAR(10),
 @billingcontact NVARCHAR(50),
 @email NVARCHAR(225),
 @password NVARCHAR(50)
AS
SET NOCOUNT ON
BEGIN
DECLARE @result int
DECLARE @EMPLOYEEID int
DECLARE @EMPLOYERID int
DECLARE @EMPLOYEENAME nvarchar(50)
DECLARE @EMPLOYEEUSERNAME nvarchar(225)
DECLARE @MEMPLOYEEID int
DECLARE @MCLIENTID int
IF EXISTS (Select ID
From dbo.Employee
Where Username = @email)

BEGIN
SET NOCOUNT ON
SELECT
@result = 1,
@EMPLOYEEID = E.ID, 
@EMPLOYERID = ClientID, 
@EMPLOYEENAME = E.Name,
@EMPLOYEEUSERNAME = E.Username
FROM dbo.Employee E inner Join dbo.Client C on E.ClientID = C.ID
Where E.Username = @email
END

ELSE
BEGIN
insert into dbo.Client(
JBGroup, 
Name,
Address,
Phone,
Email,
URL,
AccountType, 
AccountLive, 
CompanyType,
BillingContact,
DateRegisterred,
StartDate, 
EndDate
)
Values(
1,
@companyname,
@address,
@phone, 
@email,
@url,
@AccountType,
'N',
@companytype,
@billingcontact,
GETDATE(),
GETDATE(),
DATEADD(year,5, GETDATE())
)
SET @MClientID = SCOPE_IDENTITY();
insert into dbo.Employee(
ClientID,
Username, 
Password,
Level, 
Name, 
Phone,
DateCreated
)
Values(
@MClientID,
@email,
@password,
'A',
@billingcontact,
@phone,
GETDATE()
)
Set @MEmployeeID = SCOPE_IDENTITY();
INSERT dbo.JBClient
  (
   JBCLSiteID,
   JBCLName,
   JBCLAddress,
   JBCLPhone,
   JBCLEmail,
   JBCLURL,
   JBCLAccountType,
   JBCLAccountLive,
   JBCLCompanyType,
   JBCLBillingContact,
   JBCLDateRegisterred,   
   JBCLMStartDate,
   JBCLMEndDate,
   ClientID
  )
  SELECT
   JBSSiteID,
   C.Name,
   C.Address,
   C.Phone,
   C.Email, 
   C.URL,
   C.AccountType,
   C.AccountLive,
   C.CompanyType,
   C.BillingContact,
   C.DateRegisterred,
   C.StartDate, 
   C.EndDate, 
   C.ID 
FROM JBSite S CROSS join Client C
WHERE S.JBSSIteOwnerID <> 3
    AND C.[ID] = @MClientID
ORDER BY JBSSiteID ASC;
Declare @NewID int
SET @NewID = SCOPE_IDENTITY(); -- of the last one
INSERT dbo.JBEmployee
(
JBEClientID,
JBESiteID,
JBEName,
JBELevel,
JBEUsername,
JBEPassword,
JBEPhone,
EmployeeID
)
  SELECT
   @NewID + ROW_NUMBER() over (order by JBSSiteID) - COUNT(*) over (),
   JBSSiteID,
   @billingcontact,
   'A',
   @email,
   @password,
   @phone,
   @MEmployeeID
FROM JBSite
ORDER BY JBSSiteID ASC;

SELECT
 @result = 2,
 @EMPLOYEEID = E.JBEID, 
 @EMPLOYERID = E.JBEClientID, 
 @EMPLOYEENAME = C.JBClname,
 @EMPLOYEEUSERNAME = E.JBEUsername
FROM dbo.JBEmployee E inner join dbo.JBClient C on E.JBEClientID = C.JBCLID
Where JBESiteID = @siteID AND EmployeeID = @MEMPLOYEEID
END
SET NOCOUNT ON
SELECT
 @result as Result,
 @EMPLOYEEID as EMPLOYEEID, 
 @EMPLOYERID as EMPLOYERID, 
 @EMPLOYEENAME as EMPLOYEENAME,
 @EMPLOYEEUSERNAME as EMPLOYEEUSERNAME
END
GO
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros