MS SQL

MS SQL RSS

Return Boolean based on a condition in query

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2008

Posts: 1

Reputation: Smithy963 is an unknown quantity at this point

Solved Threads: 0

Smithy963

Offline

Newbie Poster

Return Boolean based on a condition in query

Mar 27th, 2009

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

 Help with Code Tags 
 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)

 Help with Code Tags 
 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.

 Help with Code Tags 
 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?

 Help with Code Tags 
 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.

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Return Boolean based on a condition in query

Apr 2nd, 2009

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

 Help with Code Tags 
 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()

Custom Application & Software Development
www.houseshark.net

•

Join Date: May 2007

Posts: 146

Reputation: Stylish is an unknown quantity at this point

Solved Threads: 14

Stylish

Offline

Junior Poster

Re: Return Boolean based on a condition in query

Apr 2nd, 2009

 Help with Code Tags 
 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.

 Help with Code Tags 
 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