Insert or Update in Store Procedure

Hello all!!!
Having problems getting my store procedure to work right. My goal is to have
my SP either update or Insert a new record in the "User_Profile" Table. I
can insert just fine, and update just fine, but not together. So if a user
name exists, it will update that row, if no name exist, then it will create
a
new row for that user.
The following is my attempt to make this work. This last attempt I know is
wrong, and I know I shouldn't have to use the IF EXISTS statement more than
once in a SP. I'm at the point of just trying any kind of combination. Any
suggestins would be great!!!
TIA!!!
Rudy
CREATE PROCEDURE NCProfile
(
--@.UPID int out,
@.username nvarchar(64),
@.Iman char(10),
@.Iwoman char(10),
@.Lman char(10),
@.Lwoman char(10),
@.BirthMonth char(10),
@.Birthdate char(10),
@.Birthyear char(10),
@.sign nvarchar(50),
@.country nvarchar(50),
@.state char(10),
@.music nvarchar(50),
@.artist NVarchar( 50 ),
@.movie NVarchar( 100 ),
@.sport nvarchar(20),
@.food nvarchar(20),
@.notwork NVarchar(100),
@.date nvarchar(50),
@.personality nvarchar(50)
)
AS
--UPDATE THIS profile
IF EXISTS(SELECT username
FROM User_Profile
WHERE username=@.username)
RETURN -1
ELSE
--BEGIN
UPDATE User_Profile
SET
Iman = @.Iman,
Iwoman = @.Iwoman,
Lman = @.Lman,
Lwoman = @.Lwoman,
birthmonth = @.birthmonth,
birthdate = @.birthdate,
birthyear = @.birthyear,
sign = @.sign,
country = @.country,
state =@.state,
music = @.music,
artist = @.artist,
movie = @.movie,
sport = @.sport,
foods = @.food,
notwork = @.notwork,
firstdate = @.date,
personality = @.personality
WHERE
username = @.username
--END
--ELSE
--BEGIN
IF EXISTS(SELECT username
FROM User_Profile
WHERE username=@.username)
RETURN 0
ELSE
INSERT INTO User_Profile (
username,
Iman,
Iwoman,
Lman,
Lwoman,
birthmonth,
birthdate,
birthyear,
sign,
country,
state,
music,
artist,
movie,
sport,
foods,
notwork,
firstdate,
personality
)
VALUES
(
@.username,
@.Iman,
@.Iwoman,
@.Lman,
@.Lwoman,
@.birthmonth,
@.birthdate,
@.birthyear,
@.sign,
@.country,
@.state,
@.music,
@.artist,
@.movie,
@.sport,
@.food,
@.notwork,
@.date,
@.personality
)
RETURN @.@.IDENTITY
--SET @.UPID = @.@.IDENTITY
--END
GOYou should probably use the Primary Key UPID to determine if you are
inserting a new record or updating an old one. This allows your code to
update username for an existing record without losing (or having to trac dow
n
and change) all FK references in the table... If you do choose this approach
,
here;s a SP that ill do that... The "IsNUll"s in the Update, combined with
adding a null default value to all input parameters allow you to pass a
subset of the input parameters for an update, and have the SP Only update th
e
record for the values of the parameters passedin and leave the others
unchanged...
ANyway, here it is...
CREATE PROCEDURE NCProfile
@.UPID int out = Null,
@.username nvarchar(64) = Null,
@.Iman char(10) = Null,
@.Iwoman char(10) = Null,
@.Lman char(10) = Null,
@.Lwoman char(10) = Null,
@.BirthMonth char(10) = Null,
@.Birthdate char(10) = Null,
@.Birthyear char(10) = Null,
@.sign nvarchar(50) = Null,
@.country nvarchar(50) = Null,
@.state char(10) = Null,
@.music nvarchar(50) = Null,
@.artist NVarchar( 50 ) = Null,
@.movie NVarchar( 100 ) = Null,
@.sport nvarchar(20) = Null,
@.food nvarchar(20) = Null,
@.notwork NVarchar(100) = Null,
@.date nvarchar(50) = Null,
@.personality nvarchar(50) = Null
As
Set NoCOunt On
If @.UPID IS NUll
Begin
Insert User_Profile(username,
Iman,Iwoman,Lman,Lwoman,
birthmonth,birthdate,birthyear,
sign,country,state,music,
artist, movie,sport,foods,
notwork,firstdate,personality)
Values(@.username, @.Iman,@.Iwoman,
@.Lman, @.Lwoman,@.birthmonth,
@.birthdate,@.birthyear,@.sign,
@.country,@.state,@.music,@.artist,
@.movie,@.sport,@.food,@.notwork,
@.date,@.personality)
Set @.UPID = Scope_Identity()
End
Else If Exists (Select * From User_Profile
Where UPID = @.UPID)
Begin
Update User_Profile Set
Iman = IsNull(@.Iman,Iman),
Iwoman = IsNull(@.Iwoman,Iwoman),
Lman = IsNull(@.Lman,Lman),
Lwoman = IsNull(@.Lwoman,Lwoman),
birthmonth = IsNull(@.birthmonth,birthmonth),
birthdate = IsNull(@.birthdate,birthdate),
birthyear = IsNull(@.birthyear,birthyear),
sign = IsNull(@.sign, sign),
country = IsNull(@.country,country),
state = IsNull(@.state, state),
music = IsNull(@.music,music),
artist = IsNull(@.artist,artist),
movie = IsNull(@.movie, movie),
sport = IsNull(@.sport,sport),
foods = IsNull(@.food,foods),
notwork = IsNull(@.notwork,notwork),
firstdate = IsNull(@.date,firstdate),
personality = IsNull(@.personality,personality)
Where UPID = @.UPID
End
Select @.UPID UPID
Return(0)
"Rudy" wrote:

> Hello all!!!
> Having problems getting my store procedure to work right. My goal is to ha
ve
> my SP either update or Insert a new record in the "User_Profile" Table. I
> can insert just fine, and update just fine, but not together. So if a user
> name exists, it will update that row, if no name exist, then it will creat
e a
> new row for that user.
> The following is my attempt to make this work. This last attempt I know is
> wrong, and I know I shouldn't have to use the IF EXISTS statement more th
an
> once in a SP. I'm at the point of just trying any kind of combination. An
y
> suggestins would be great!!!
> TIA!!!
> Rudy
> CREATE PROCEDURE NCProfile
> (
> --@.UPID int out,
> @.username nvarchar(64),
> @.Iman char(10),
> @.Iwoman char(10),
> @.Lman char(10),
> @.Lwoman char(10),
> @.BirthMonth char(10),
> @.Birthdate char(10),
> @.Birthyear char(10),
> @.sign nvarchar(50),
> @.country nvarchar(50),
> @.state char(10),
> @.music nvarchar(50),
> @.artist NVarchar( 50 ),
> @.movie NVarchar( 100 ),
> @.sport nvarchar(20),
> @.food nvarchar(20),
> @.notwork NVarchar(100),
> @.date nvarchar(50),
> @.personality nvarchar(50)
> )
> AS
> --UPDATE THIS profile
> IF EXISTS(SELECT username
> FROM User_Profile
> WHERE username=@.username)
> RETURN -1
> ELSE
> --BEGIN
> UPDATE User_Profile
> SET
> Iman = @.Iman,
> Iwoman = @.Iwoman,
> Lman = @.Lman,
> Lwoman = @.Lwoman,
> birthmonth = @.birthmonth,
> birthdate = @.birthdate,
> birthyear = @.birthyear,
> sign = @.sign,
> country = @.country,
> state =@.state,
> music = @.music,
> artist = @.artist,
> movie = @.movie,
> sport = @.sport,
> foods = @.food,
> notwork = @.notwork,
> firstdate = @.date,
> personality = @.personality
> WHERE
> username = @.username
> --END
> --ELSE
> --BEGIN
> IF EXISTS(SELECT username
> FROM User_Profile
> WHERE username=@.username)
> RETURN 0
> ELSE
>
> INSERT INTO User_Profile (
> username,
> Iman,
> Iwoman,
> Lman,
> Lwoman,
> birthmonth,
> birthdate,
> birthyear,
> sign,
> country,
> state,
> music,
> artist,
> movie,
> sport,
> foods,
> notwork,
> firstdate,
> personality
> )
> VALUES
> (
> @.username,
> @.Iman,
> @.Iwoman,
> @.Lman,
> @.Lwoman,
> @.birthmonth,
> @.birthdate,
> @.birthyear,
> @.sign,
> @.country,
> @.state,
> @.music,
> @.artist,
> @.movie,
> @.sport,
> @.food,
> @.notwork,
> @.date,
> @.personality
> )
> RETURN @.@.IDENTITY
> --SET @.UPID = @.@.IDENTITY
> --END
> GO
>|||Hi
IF EXISTS(SELECT username
FROM User_Profile
WHERE username=@.username)
RETURN -1
Will return if the username exists, if it does not exist it will try to
update an non-existant record!
Try:
CREATE PROCEDURE NCProfile
(
--@.UPID int out,
@.username nvarchar(64),
@.Iman char(10),
@.Iwoman char(10),
@.Lman char(10),
@.Lwoman char(10),
@.BirthMonth char(10),
@.Birthdate char(10),
@.Birthyear char(10),
@.sign nvarchar(50),
@.country nvarchar(50),
@.state char(10),
@.music nvarchar(50),
@.artist NVarchar( 50 ),
@.movie NVarchar( 100 ),
@.sport nvarchar(20),
@.food nvarchar(20),
@.notwork NVarchar(100),
@.date nvarchar(50),
@.personality nvarchar(50)
)
AS
--UPDATE THIS profile
IF EXISTS(SELECT username
FROM User_Profile
WHERE username=@.username)
BEGIN
UPDATE User_Profile
SET
Iman = @.Iman,
Iwoman = @.Iwoman,
Lman = @.Lman,
Lwoman = @.Lwoman,
birthmonth = @.birthmonth,
birthdate = @.birthdate,
birthyear = @.birthyear,
sign = @.sign,
country = @.country,
state =@.state,
music = @.music,
artist = @.artist,
movie = @.movie,
sport = @.sport,
foods = @.food,
notwork = @.notwork,
firstdate = @.date,
personality = @.personality
WHERE
username = @.username
RETURN -1
END
ELSE
BEGIN
INSERT INTO User_Profile (
username,
Iman,
Iwoman,
Lman,
Lwoman,
birthmonth,
birthdate,
birthyear,
sign,
country,
state,
music,
artist,
movie,
sport,
foods,
notwork,
firstdate,
personality
)
VALUES
(
@.username,
@.Iman,
@.Iwoman,
@.Lman,
@.Lwoman,
@.birthmonth,
@.birthdate,
@.birthyear,
@.sign,
@.country,
@.state,
@.music,
@.artist,
@.movie,
@.sport,
@.food,
@.notwork,
@.date,
@.personality
)
RETURN SCOPE_IDENTITY()
END
GO
You should add error checking, and it would be better to use the output
parameter you commented out for the identity value.
You may want to consider passing in the identity value as suggested by
CBretana
John
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:E8398452-380E-4213-907E-C31AB0BA8705@.microsoft.com...
> Hello all!!!
> Having problems getting my store procedure to work right. My goal is to
> have
> my SP either update or Insert a new record in the "User_Profile" Table. I
> can insert just fine, and update just fine, but not together. So if a user
> name exists, it will update that row, if no name exist, then it will
> create a
> new row for that user.
> The following is my attempt to make this work. This last attempt I know is
> wrong, and I know I shouldn't have to use the IF EXISTS statement more
> than
> once in a SP. I'm at the point of just trying any kind of combination.
> Any
> suggestins would be great!!!
> TIA!!!
> Rudy
> CREATE PROCEDURE NCProfile
> (
> --@.UPID int out,
> @.username nvarchar(64),
> @.Iman char(10),
> @.Iwoman char(10),
> @.Lman char(10),
> @.Lwoman char(10),
> @.BirthMonth char(10),
> @.Birthdate char(10),
> @.Birthyear char(10),
> @.sign nvarchar(50),
> @.country nvarchar(50),
> @.state char(10),
> @.music nvarchar(50),
> @.artist NVarchar( 50 ),
> @.movie NVarchar( 100 ),
> @.sport nvarchar(20),
> @.food nvarchar(20),
> @.notwork NVarchar(100),
> @.date nvarchar(50),
> @.personality nvarchar(50)
> )
> AS
> --UPDATE THIS profile
> IF EXISTS(SELECT username
> FROM User_Profile
> WHERE username=@.username)
> RETURN -1
> ELSE
> --BEGIN
> UPDATE User_Profile
> SET
> Iman = @.Iman,
> Iwoman = @.Iwoman,
> Lman = @.Lman,
> Lwoman = @.Lwoman,
> birthmonth = @.birthmonth,
> birthdate = @.birthdate,
> birthyear = @.birthyear,
> sign = @.sign,
> country = @.country,
> state =@.state,
> music = @.music,
> artist = @.artist,
> movie = @.movie,
> sport = @.sport,
> foods = @.food,
> notwork = @.notwork,
> firstdate = @.date,
> personality = @.personality
> WHERE
> username = @.username
> --END
> --ELSE
> --BEGIN
> IF EXISTS(SELECT username
> FROM User_Profile
> WHERE username=@.username)
> RETURN 0
> ELSE
>
> INSERT INTO User_Profile (
> username,
> Iman,
> Iwoman,
> Lman,
> Lwoman,
> birthmonth,
> birthdate,
> birthyear,
> sign,
> country,
> state,
> music,
> artist,
> movie,
> sport,
> foods,
> notwork,
> firstdate,
> personality
> )
> VALUES
> (
> @.username,
> @.Iman,
> @.Iwoman,
> @.Lman,
> @.Lwoman,
> @.birthmonth,
> @.birthdate,
> @.birthyear,
> @.sign,
> @.country,
> @.state,
> @.music,
> @.artist,
> @.movie,
> @.sport,
> @.food,
> @.notwork,
> @.date,
> @.personality
> )
> RETURN @.@.IDENTITY
> --SET @.UPID = @.@.IDENTITY
> --END
> GO
>|||Thank you John and C!
"John Bell" wrote:

> Hi
> IF EXISTS(SELECT username
> FROM User_Profile
> WHERE username=@.username)
> RETURN -1
> Will return if the username exists, if it does not exist it will try to
> update an non-existant record!
> Try:
> CREATE PROCEDURE NCProfile
> (
> --@.UPID int out,
> @.username nvarchar(64),
> @.Iman char(10),
> @.Iwoman char(10),
> @.Lman char(10),
> @.Lwoman char(10),
> @.BirthMonth char(10),
> @.Birthdate char(10),
> @.Birthyear char(10),
> @.sign nvarchar(50),
> @.country nvarchar(50),
> @.state char(10),
> @.music nvarchar(50),
> @.artist NVarchar( 50 ),
> @.movie NVarchar( 100 ),
> @.sport nvarchar(20),
> @.food nvarchar(20),
> @.notwork NVarchar(100),
> @.date nvarchar(50),
> @.personality nvarchar(50)
> )
> AS
> --UPDATE THIS profile
> IF EXISTS(SELECT username
> FROM User_Profile
> WHERE username=@.username)
> BEGIN
> UPDATE User_Profile
> SET
> Iman = @.Iman,
> Iwoman = @.Iwoman,
> Lman = @.Lman,
> Lwoman = @.Lwoman,
> birthmonth = @.birthmonth,
> birthdate = @.birthdate,
> birthyear = @.birthyear,
> sign = @.sign,
> country = @.country,
> state =@.state,
> music = @.music,
> artist = @.artist,
> movie = @.movie,
> sport = @.sport,
> foods = @.food,
> notwork = @.notwork,
> firstdate = @.date,
> personality = @.personality
> WHERE
> username = @.username
> RETURN -1
> END
> ELSE
> BEGIN
> INSERT INTO User_Profile (
> username,
> Iman,
> Iwoman,
> Lman,
> Lwoman,
> birthmonth,
> birthdate,
> birthyear,
> sign,
> country,
> state,
> music,
> artist,
> movie,
> sport,
> foods,
> notwork,
> firstdate,
> personality
> )
> VALUES
> (
> @.username,
> @.Iman,
> @.Iwoman,
> @.Lman,
> @.Lwoman,
> @.birthmonth,
> @.birthdate,
> @.birthyear,
> @.sign,
> @.country,
> @.state,
> @.music,
> @.artist,
> @.movie,
> @.sport,
> @.food,
> @.notwork,
> @.date,
> @.personality
> )
> RETURN SCOPE_IDENTITY()
> END
> GO
> You should add error checking, and it would be better to use the output
> parameter you commented out for the identity value.
> You may want to consider passing in the identity value as suggested by
> CBretana
> John
> "Rudy" <Rudy@.discussions.microsoft.com> wrote in message
> news:E8398452-380E-4213-907E-C31AB0BA8705@.microsoft.com...
>
>