[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

Is there a better way - SQL SP

Hello Experts,

I'm wondering if there is a better way of writing this -

CREATE PROCEDURE [dbo].[NewJobboardEmployerNewAccountUser]
@SiteID int,
@ClientID int,
@Name nvarchar(50),
@Username nvarchar(255),
@Password nvarchar(50),
@Level nvarchar(50)
AS
SET NOCOUNT ON;
DECLARE @EmployeeID int
BEGIN
IF Exists(Select ID from dbo.Employee where Username = @Username)
BEGIN
Select Result = 1
END
ELSE
BEGIN
INSERT INTO dbo.Employee(ClientID, Username, [Password], [Level], [Name], DateCreated)
Values(@ClientID, @Username, @Password, @Level, @Name, GETDATE())
SET @EmployeeID = SCOPE_IDENTITY()
IF Exists(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'JBEmployee')
BEGIN
INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEUsername, JBEPassword, JBELevel, JBEName, JBEDateCreated, JBEUserEmail, EmployeeID, ClientID)
SELECT JBCLID, JBCLSiteID, @Username, @Password, @Level, @Name, GETDATE(), @Username, @EmployeeID, @ClientID
FROM dbo.JBClient WHERE ClientID = @ClientID
END
SELECT  Result = 2, EmployeeID = @EmployeeID
END
END

GO

Open in new window


As when I call it from an ASP page, i get the following error --

File Name: /coms/em/newuseraccount.asp Line Number: 24
Description: Application uses a value of the wrong type for the current operation.

Line 24 is (on a HTML email template) -

Userdetail_cmd.Parameters.Append Userdetail_cmd.CreateParameter("param1", 5, 1, -1, Userdetail__MMColParam) ' adDouble

Open in new window


MMColParam is Request("ID")

Params are sent by the page that runs the SQL stored Procedure -

myMail.CreateMHTMLBody URLLong

Open in new window


URLLong is -

URLLong= (SiteDetails("JBSURL")) & "/coms/em/newuseraccount.asp?ID=" & UserAdd("EmployeeID") & "Site=" & Session("SITEID")

Open in new window


UserAdd("EmployeeID")  Should be the third from bottom line of the SP -

SELECT  Result = 2, EmployeeID = @EmployeeID

Open in new window


But... I'm getting errors :(

Help!! Please :)

Thanks
0
garethtnash
Asked:
garethtnash
2 Solutions
 
Anthony PerkinsCommented:
The line you posted has a parameter "param1" defined as 5 (adDouble) since you do not have any parameters in your Stored Procedure called param1 or declared as float, I would suggest you fix that first.
0
 
sachitjainCommented:
CREATE PROCEDURE [dbo].[NewJobboardEmployerNewAccountUser]
@SiteID int,
@ClientID int,
@Name nvarchar(50),
@Username nvarchar(255),
@Password nvarchar(50),
@Level nvarchar(50)
AS
      SET NOCOUNT ON;
      DECLARE @EmployeeID int
      DECLARE @Result int
      BEGIN
            IF Exists(Select ID from dbo.Employee where Username = @Username)
            BEGIN
                  Set @Result = 1
            END
            ELSE
            BEGIN
                  INSERT INTO dbo.Employee(ClientID, Username, [Password], [Level], [Name], DateCreated)
                  Values(@ClientID, @Username, @Password, @Level, @Name, GETDATE())
                  SET @EmployeeID = SCOPE_IDENTITY()
                  IF Exists(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'JBEmployee')
                  BEGIN
                        INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEUsername, JBEPassword, JBELevel, JBEName, JBEDateCreated, JBEUserEmail, EmployeeID, ClientID)
                        SELECT JBCLID, JBCLSiteID, @Username, @Password, @Level, @Name, GETDATE(), @Username, @EmployeeID, @ClientID
                        FROM dbo.JBClient WHERE ClientID = @ClientID
                  END
                  Set @Result = 2
                  Select @Result Result, @EmployeeID EmployeeId
            END
      END
GO
0
 
garethtnashAuthor Commented:
Thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now