CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Emmanuel Gabion
Emmanuel Gabion

Reputation: 425

Returning a string after inserting data into SQL through a stored procedure

I'm new to SQL and my first time to create a stored procedure.

I was supposed to insert data and get a return string. The return value should be Successfully inserted or if failed, should notify me if a username already exists.. or something like that however

I'm stuck in codes for error:

So here is my code..

Create Proc Mock_InsertUser 
(
@Username varchar(20),
@Password varchar(100), 
@fullName varchar(60),
@Activated bit, 
@Suspended bit 
)
as
Begin
  DECLARE 
    @Error INT,
    @rowcount INT,
    @log varchar(1000)

IF EXISTS(SELECT username FROM Users_mock WHERE username = @Username)
BEGIN
    SET @log = 'Username ' + @Username + ' already exists.'
    GOTO ERROR  
END

Insert into Users_mock 
  (username, [password], full_name, Activated, Suspended)
values 
  (@Username, @Password, @fullName, @Activated, @Suspended)

SELECT @Error = @@ERROR, @rowcount = @@ROWCOUNT

--if there is an error OR If row is not inserted..
IF @Error <> 0 OR @rowcount < 1
BEGIN
   SET @log = 'Failed to Insert Account.'
   GOTO ERROR
END

-----------------------------------------------------------
Error:

--Some code here
END

Sorry for lousy question :)

Upvotes: 2

Views: 4240

Answers (2)

Captain Kenpachi
Captain Kenpachi

Reputation: 7215

use the ELSE keyword:

IF EXISTS(SELECT username FROM Users_mock WHERE username = @Username)
BEGIN
    SET @log = 'Username ' + @Username + ' already exists.'
    SELECT @log --this end the stored procedure. it is like the return keyword.
END
ELSE
BEGIN
    Insert into Users_mock 
    (username, [password], full_name, Activated, Suspended)
    values 
    (@Username, @Password, @fullName, @Activated, @Suspended)

    DECLARE @Error = @@ERROR 
    DECLARE @rowcount = @@ROWCOUNT 

    --if there is an error OR If row is not inserted..
    IF @Error <> 0 OR @rowcount < 1
    BEGIN
        SET @log = 'Failed to Insert Account.'
        SELECT @log
    END
END

Upvotes: 1

marc_s
marc_s

Reputation: 754388

Why don't you use something like this:

CREATE PROCEDURE dbo.Mock_InsertUser
       (@Username VARCHAR(20),
        @Password VARCHAR(100),
        @fullName VARCHAR(60),
        @Activated BIT,
        @Suspended BIT )
AS 
BEGIN
    BEGIN TRY
       DECLARE @Error INT, @rowcount INT, @log VARCHAR(1000)

       -- check if user exists    
       IF EXISTS (SELECT * FROM dbo.Users_mock WHERE username = @Username) 
       BEGIN
          -- yes - just define the response message
          SET @log = 'Username ' + @Username + ' already exists.'
       END
       ELSE BEGIN      
          -- doesn't exist - insert data
          INSERT INTO 
              dbo.Users_mock(username, [password], full_name, Activated, Suspended)
          VALUES
              (@Username, @Password, @fullName, @Activated, @Suspended)

          -- set response message
          SET @log = 'Username ' + @Username + ' successfully inserted.'
       END
    END TRY
    BEGIN CATCH
       -- handle exceptions - you can access error details here, too, if you need to
       SET @log = 'Some really weird error happened.'
    END CATCH  

    -- return the message back to the caller    
    SELECT @Log
END

Points to see:

  • use BEGIN TRY/END TRY - BEGIN CATCH/END CATCH like in C# to handle exceptions - no need to constantly check for @@ERROR values ....

  • basically just check for existence of the user - set the @Log message accordingly. If user doesn't exist, insert it and set @Log message to "success"

  • no need for label, GOTO and other stuff like that....

Upvotes: 1

Related Questions