Ad:

MS SQL Discussion Thread
Unsolved
Views: 7941

Mar 27th, 2009

Return Boolean based on a condition in query

Expand Post »

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

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE CreateWebsiteMember
@wbmName
AS
INSERT INTO tblWebsiteMember (wbmName)
VALUES (@wbmName)
CREATE PROCEDURE CreateWebsiteMember
@wbmName
AS
Insert into tblWebsiteMember (wbmName)
values (@wbmName)

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE GetWebsiteMember
@wbmName
AS
SELECT Name
FROM tblWebsiteMembers
WHERE Name = @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.

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE CreateWebsiteMember
@wbmName
AS
INSERT INTO tblWebsiteMember (wbmName)
VALUES (@wbmName)
SELECT 'True' AS Response
FROM tblWebsiteMember
WHERE UserName = @wbmUN
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?

MS SQL Syntax (Toggle Plain Text)
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'
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.

Similar Threads

JSP database connectivity according to Model View Controller (MVC) Model 2 in JSP

Smithy963

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Dec 2008

Permalink

Apr 2nd, 2009

Re: Return Boolean based on a condition in query

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

MS SQL Syntax (Toggle Plain Text)
 
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),
@ID int OUTPUT
)
AS
Insert into tblWebsiteMember (wbmName)
values (@wbmName)

set @ID = SCOPE_IDENTITY()

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Apr 2nd, 2009

Re: Return Boolean based on a condition in query

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE CreateWebsiteMember
(
  @wbmName varchar(50)
)
AS
INSERT INTO tblWebsiteMember (Name) VALUES(@wbmName)
SELECT ID AS 'Result' FROM tblWebsiteMember WHERE NAME = @wbmName
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.

MS SQL Syntax (Toggle Plain Text)
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
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

Last edited by Stylish; Apr 2nd, 2009 at 6:39 pm.

Stylish

Reputation Points: 44

Solved Threads: 19

Junior Poster

Offline

148 posts
since May 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: how to “store” stored procedures

Next Thread in MS SQL Forum Timeline: sp_changedbowner question

DaniWeb Message

Cancel Changes

User Name
Password