Hi guys,

I've been programming for quite a while now and just jumped in to SQL and Stored Procedures due to a Uni project I have due in.

I have a table called tblWebsiteMember and tblWebsiteMember has 2 Columns - ID and Name

CREATE PROCEDURE CreateWebsiteMember
@wbmName
AS
Insert into tblWebsiteMember (wbmName)
values (@wbmName)
CREATE PROCEDURE GetWebsiteMember
@wbmName
AS
select Name
from tblWebsiteMembers
where Name = @wbmName

To create a member I execute CreateWebsiteMember and give the website member a name, then to check if the website member was added successfully I execute GetWebsiteMember with the newly created members name, all works fine.

My project is web based and I would prefer using 1 query in place of 2. So I figured I needed a way to return data on the query as a kind of validation.

CREATE PROCEDURE CreateWebsiteMember
@wbmName
AS
Insert into tblWebsiteMember (wbmName)
values (@wbmName)
select 'True' as Response
from tblWebsiteMember
where UserName = @wbmUN

How do I return False in this example?

CREATE PROCEDURE CreateWebsiteMember
@wbmName
AS
Insert into tblWebsiteMember (wbmName)
values (@wbmName)
if @wbmName = (select Name From tblWebsiteMember where Name = @wbmName)
return 'True'
else
Return 'False'

A bit of a mish mash of languages here but I hope it gets the point across.

I cannot understand any learning resources out there for SQL, they seem to be very scare and complex compared to programming tutorials, but if anyone knows of a simple, similar tutorial for this, please do point me there.

Add an additional parameter for your id, this will return the newly inserted record's id, otherwise if it failed an exception will be thrown and you don't need to check for its existence

CREATE PROCEDURE CreateWebsiteMember
(
@wbmName varchar(50),
@ID int OUTPUT
)
AS
Insert into tblWebsiteMember (wbmName)
values (@wbmName)

set @ID = SCOPE_IDENTITY()
CREATE PROCEDURE CreateWebsiteMember
(
  @wbmName varchar(50)
)
AS
INSERT into tblWebsiteMember (Name) VALUES(@wbmName)
SELECT ID as 'Result' FROM tblWebsiteMember where NAME = @wbmName

Now check 'Result' for NULL value in your web application. At least, that is how I would do it.

CREATE PROCEDURE CreateWebsiteMember
(
  @wbmName varchar(50)
)
AS
INSERT into tblWebsiteMember (Name) VALUES(@wbmName)

IF EXISTS (SELECT 1 FROM tblWebsiteMember WHERE Name = @wbmName)
  SELECT 'Result'=1
ELSE
  SELECT 'Result'=0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.