output as flag - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

output as flag

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-02-02 : 01:32:02

Hi all,

This is the senario,

my employee table consists of id,firstname,middlename,lastname and more details

if i enter the firstname,middlename and lastname
if the name is already exists means it should return 0 or else 1

can any one help me in writing the stored procedure pls.
The input arguments for the SP is firstname,middlename and the lastname.

Thanks in advance,
Kiranmayee

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-02 : 12:08:45

I assume you have a unique constraint on your table for those columns. So one way is to do something like:

CREATE PROCEDURE <ProcName>
(
    <Parameter List>,
    @OutputFlag BIT OUTPUT
)
BEGIN TRY
    SET @OutputFlag = 0
    
    <Insert Statement>
    
    SET @OutputFlag = 1
END TRY
BEGIN CATCH
    <Do something>
END CATCH

If you wanted it as a RETURN code (probably not the best way) you could do soemthing like

CREATE PROCEDURE <ProcName>
(
    <Parameter List>
)
BEGIN TRY

    <Insert Statement>
    
    RETURN CAST(1 AS BIT)
END TRY
BEGIN CATCH
    RETURN CAST(0 AS BIT)
END CATCH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-02 : 12:35:48

This is a simple predicate; why do you want to put it into a procedure that returns a flag? There are more interesting ways to make your SQL run slower and become harder to maintain.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources