How do I return a TRUE or FALSE using a SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I return a TRUE or FALSE using a SP?

Author

Topic

DavidBeoulve
Starting Member

3 Posts

Posted - 2005-10-14 : 12:19:20

How do I return a TRUE or FALSE using a Stored Procedure?

This is my SQL Server Code:
All I need it to do is return a TRUE or FALSE response (in integer, string, I don't care what manner) as to whether or not the given select statement returned with data (in other words, if something met the criteria).

The criteria is "Do any records nameKey = 52 and were they posted on today's date?"

---------------------------------------
CREATE PROCEDURE udspCheckForMilestoneUpdates
AS

IF EXISTS
(
SELECT *
FROM storyData
WHERE nameKey = 52
AND
date_posted = CONVERT(varchar, DATEADD([year], 0, GETDATE()), 101)
)
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
GO
---------------------------------------

In SQL Query Analyzer, when I "EXEC udspCheckForMilestoneUpdates" it says:
"The command(s) completed successfully."

However, the Grid tab shows there is no data, and my C# code's DataSet has no data put into it...

Here the C# for those interested:
---------------------------------------
DataSet dsAnswer;
string StringConnection;
StringConnection = ConfigurationSettings.AppSettings["ConnectionStringStoryDB"];

dsAnswer = SqlHelper.ExecuteDataset(StringConnection,CommandType.StoredProcedure,"storedprocCheckForMilestoneUpdates");

int intCatcher = Convert.ToInt32(dsAnswer.Tables[0].Rows[0][0].ToString());

if (intCatcher)...
---------------------------------------

Any help is greatly appreciated.

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 13:22:15

In Query Analyser:

DECLARE @MyReturn int
EXEC @MyReturn = udspCheckForMilestoneUpdates
SELECT @MyReturn AS [Return Value]

From C# you can "capture" the return value from the SProc.

Alternatively you can "return" the value in a recordset:


CREATE PROCEDURE udspCheckForMilestoneUpdates
AS

SELECT [MyResult] = CASE WHEN EXISTS
(
    SELECT *
    FROM storyData
    WHERE nameKey = 52
          AND date_posted = CONVERT(varchar, DATEADD([year], 0, GETDATE()), 101)
)
    THEN 1
    ELSE 0
    END
GO

Call with:
EXEC udspCheckForMilestoneUpdates

or you could use an Output Parameter (which is similar to the return value, but can return any data type, and you can have multiple OUTPUT parameters:


CREATE PROCEDURE udspCheckForMilestoneUpdates
    @MyOutParmeter int OUTPUT
AS

IF EXISTS
(
    SELECT *
    FROM storyData
    WHERE nameKey = 52
          AND date_posted = CONVERT(varchar, DATEADD([year], 0, GETDATE()), 101)
)
BEGIN
    SELECT @MyOutParmeter = 1
END
ELSE
BEGIN
    SELECT @MyOutParmeter = 0
END
GO

Call with:
DECLARE @MyReturn int
EXEC udspCheckForMilestoneUpdates @MyOutParmeter = @MyReturn OUTPUT
SELECT @MyReturn AS [Return Value]

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-14 : 13:22:44

Instead of "Return 1" or "Return 0", use SELECT 1 or SELECT 0.

YOu can even say:

SELECT CASE WHEN EXISTS(....) THEN 1 ELSE 0 END as Result

Important: In C#, when calling a stored proc that returns a single row/column result, be sure to use ExecuteScalar() -- do NOT open up a dataset/datatable! It's a huge waste of resources when you only need 1 value back.

DavidBeoulve
Starting Member

3 Posts

Posted - 2005-10-14 : 14:10:54

Awesomeness guys! This got it working!
Thank you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources