Conditional SP/Functions - 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 2005 Forums

Transact-SQL (2005)

Conditional SP/Functions

Author

Topic

hazemfadl
Starting Member

1 Post

Posted - 2008-07-12 : 20:28:46

hi all,
I need your help ...

If I have the following tables:
1. Ar (ArID – ArTitle)
2. Author (AuhtID – AuthName)
3. ArAuthor (ArID – AuthID)
And I want to implement the following as function or SP:
When I try to add AuthorName to Ar (Inputs: ArID, AuthName)
1.Check if the AuthName Exists in Author
a. If (Yes):
a1. Add ArID and AuthID to ArAuthor
b.If (No):
b1. Add Author to Author table
b2. Add ArID and new AuthID to ArAuthor
2.Returns AuthID

This to prohibit AuthName Duplication in Author Table

Thank you in advanced,

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-12 : 22:00:31

You can do that with constraints (pkey, fkey, unique, and so).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-13 : 02:28:35

If you want these inserts also happening at once, you can do it like this

CREATE PROCEDURE InsertData
@ArID int,
@AuthName varchar(100),
@AuthID int=0 OUTPUT
AS
IF EXISTS(SELECT 1 FROM Author WHERE AuthName=@AuthName)
BEGIN
SELECT @AuthID=AuthID
FROM Author
WHERE AuthName=@AuthName
INSERT INTO ArAuthor(ArID ,AuthID)
SELECT @ArID,@AuthID
END
ELSE
BEGIN
INSERT INTO Author (AuthName)
SELECT @AuthName

SELECT @AuthID=SCOPE_IDENTITY()
INSERT INTO ArAuthor(ArID ,AuthID)
SELECT @ArID,@AuthID
END

and the you can use it like this

DECLARE @retAuthID int
EXEC InsertData ArIDValue,AuthNamevalue,@retAuthID
SELECT @retAuthID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources