stored proc - 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 2000 Forums

SQL Server Development (2000)

stored proc

Author

Topic

urbantruth
Starting Member

4 Posts

Posted - 2005-07-21 : 14:40:00

what is the correct way to use a stored proc to insert data into a table with aa where clause? this is what I have and it dosen't work.

insert into tblLifeGroupMembers
(
LifeGroupID

)
values
(
@LifeGroupID

)
where
(
PeopleID=@LGLeaderID
)

insert into tblLifeGroupMembers
(
LifeGroupID
)
values
(
@LifeGroupID
)
where
(
PeopleID=@LGNetworkerID
)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-21 : 14:51:02

Why your LifegroupID is not an Identity, I dont know.

Can you post the actual SP?

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

urbantruth
Starting Member

4 Posts

Posted - 2005-07-21 : 15:17:36

well I'm trying to do an insert when i should be doing an update.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-07-21 : 15:21:26

Donatwork:
There is no need to be rude. In many cases identity columns just dont work for the business rules you have in place. He may have a business rule that says the ID needs to contain alpha or that it needs to be a uniqueidentifier.

urbantruth:
The SQL language does not support a "where" clause on an insert statement when the values is used.
You would need something like this:
Insert into tblLifeGroupMembers
Select LifeGroupID
from [YOURTABLEHERE]
where PeopleID = @LGNetworkerID

Daniel
SQL Server DBA
www.dallasteam.com

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-07-21 : 15:23:51

quote:
Originally posted by urbantruth

well I'm trying to do an insert when i should be doing an update.

Yeah, you have four basic types of statements:

SELECT
UPDATE
INSERT
DELETE

Books on-line is a good source to read about each and it comes free with sql server.

Daniel
SQL Server DBA
www.dallasteam.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-21 : 15:35:50

Dan,

I didn't get the impression that Don was being rude.

Don,

Dan's right. There are a myriad of reasons NOT to use an identity column for the primary key.

UrbanTruth,

We still need to see that stored procedure code.

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-07-21 : 15:53:33

quote:
Originally posted by Bustaz Kool

Dan,

I didn't get the impression that Don was being rude.

Don,

Dan's right. There are a myriad of reasons NOT to use an identity column for the primary key.

UrbanTruth,

We still need to see that stored procedure code.

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

Well I appolgize for any hurt that I may have caused Don. In another one of his posts he said that his "magic 8 ball was not working today" and that the poster needed to post more information but in fact there was plenty to go from by what was posted. I felt that to be a tad rude as well but didnt say anything because I'm sure it was meant to be funny but still was not taken that way by me and I do understand that the majority of my genetics are from poland so I probably wouldnt find most things funny :D. In this case I just found the comment to be completely unnecessary. Maybe its just the way it was worded. To me it sounded quite sarcastic and seemed like he was poking fun at the poster. I dunno I guess thats just me.

Daniel
SQL Server DBA
www.dallasteam.com

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-07-21 : 15:58:53

you are trying to do an update instead of a insert? Or you are trying to do an update if the row exists, else an insert?


if exists (select * from tblLifeGroupMembers where PeopleID=@LGLeaderID)
  begin
  update tblLifeGroupMembers
  set LifeGroupID = @LifeGroupID
  where PeopleID=@LGLeaderID
end
else
  begin
  insert into tblLifeGroupMembers
  (LifeGroupID) values (@LifeGroupID)
end

Of course, there are many ways of doing this...


update tblLifeGroupMembers
set LifeGroupID = @LifeGroupID
where PeopleID=@LGLeaderID
if @@RowCount = 0
  begin
  insert into tblLifeGroupMembers
  (LifeGroupID) values (@LifeGroupID)
end

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-22 : 13:21:07

Yes, i am VERY sarcastic.

I understand that IDENTITY columns dont always fit business rules. Sometimes complex business logic prohibits it. I saw no complex logic, hence was being sarcastic.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources