CODEWITHSUNDEEP
c#asp.net.netsql-servert-sql
Jack
Jack

Reputation: 7547

How do I design my method's return type and stored procedure's return value?

Let's take basic scenario where I want to insert a record during user registration only if no username exists in the database.

My question is will you create 2 separate stored procedures and make 2 calls to the database one for checking whether the username exists or not and 2nd one to actually insert into the database or will you create one stored procedure and write both the queries inside that only?

If you create one stored procedure, then my 2nd question what should you actually return from stored procedure? I normally return hard coded numbers from stored procedures and then check inside code. Is this a good practise?

Upvotes: 1

Views: 605

Answers (4)

huhu78
huhu78

Reputation: 389

MERGE statement may be helpful. Let say MERGE is conditional INSERT/UPDATE. In single statement you can INSERT new user if it does not exist or just UPDATE it if exists.

As written by other folks: stored procedure is much better then dynamic SQL. Wrap MERGE into CREATE PROCEDURE.

In real world, user interaction / workflow in your app / webapp will tell you if you need one or two procedures.

Scenario A)

  • Give me your user data
  • I will create an account for you or just update it (one stored procedure)

In scenario A do MERGE for conditional INSERT/UPDATE.

Scenario B)

  • Give me your user data and decide if you want to sing up or just login
  • (You want to sign up) - Checking if given login is free (first stored procedure or just query)
  • (It's free; Sign up!) - Creating an account, but have to check again if login is still free (second strored procedure)

In scenario B you may check for login existance and then do INSERT or (better) do INSERT in TRY .. CATCH block. You can do RAISERROR in CATCH block with useful message / state - so you can report problem with creating account by throwing "exception" from SQL (instead of return param) and catching it in your application code. This logic may be more useful in coding.

Upvotes: 0

Darren
Darren

Reputation: 70728

You could do something like:

CREATE PROCEDURE AddNewUser
(
  @Username         VARCHAR(30)
, @Password         VARCHAR(30)
, @UserExists       BIT OUTPUT
)

AS

-- CHECK IF THE USER EXISTS:
DECLARE @RowCount INT

SELECT @RowCount = COUNT(*) 
FROM Users
WHERE Username = @Username


IF (@RowCount > 0)
BEGIN
   SET @UserExists = 1
END
ELSE

BEGIN

   SET @UserExists = 0

   INSERT INTO Users
     (Username, [Password])
   VALUES
     (@Username, @Password)
END

GO

Then in the application you could use the @UserExists parameter, 1 would indicate that the user already exists, 0 would indicate that the user did not exist and has been created.

For good practice you should use stored procedures and not inline SQL as you will be vulnerable to SQL injection attacks.

Upvotes: 1

Lucero
Lucero

Reputation: 60190

This operation ultimately needs to be "atomic" - the check cannot be detached from the actual creation or you might run into concurrency issues. While you can handle some of it with transactions and locking accross two or more SPs, the best way IMHO is to use one SP and perform the check at the same time (in the same statement) as the insert occurs.

I'd return a recordset with the full record of the inserted user, and raise an error if there is a name conflict.

Upvotes: 4

Iridio
Iridio

Reputation: 9271

I will do one stored procedure that will return the ID of the newly inserted user or -1 if no insert occured

Upvotes: 3

Related Questions