Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS SQL Switch Return Value off

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

Open in new window

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

In my SP how do I stop the results creating a Return Value output?
There is always a RETURN parameter, whether you declare it or not.  If you don't need it then ignore it.
Having said that post your ASP code and we should be able to point out the problem.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of garethtnash

ASKER

Thank you