Avatar of garethtnash
garethtnash
Flag 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

Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
garethtnash

8/22/2022 - Mon
Anthony Perkins

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.
Anthony Perkins

Having said that post your ASP code and we should be able to point out the problem.
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
garethtnash

ASKER
Thank you
Your help has saved me hundreds of hours of internet surfing.
fblack61