?
Solved

Stored Procedure Update Table where @parameter = 1 sql 2005

Posted on 2008-11-05
4
Medium Priority
?
439 Views
Last Modified: 2012-08-13
I am passing in a variable @processid to a stored procedure and I want to set a field in a table to 1 based on the value

I keep getting errors and I dont know what is wrong.

help with my code please
@processid int
 
update sales_jobentry 
case 
when  @processid = 0 then set [0] = 1
when  @processid =  1 then set [1] = 1
when  @processid =  2 then set [2] = 1
when  @processid =  3 then set [3] = 1
when  @processid =  4 then set [4] = 1
when  @processid =  5 then set [5] = 1
when  @processid =  6 then set [6] = 1
when  @processid =  7 then set [7] = 1
when  @processid =  8 then set [8] = 1
end 
where jobnumber = @jobnumber

Open in new window

0
Comment
Question by:logoncom
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:3abqari
ID: 22889964
What's the structure of the sales_jobentry?
0
 
LVL 3

Assisted Solution

by:3abqari
3abqari earned 200 total points
ID: 22889974
and where are you declaring the @jobnumber?
0
 

Author Comment

by:logoncom
ID: 22890358
the number in [ ] are fields in my table that I want to set to 1 or 0.

I'm trying to accomplish this using a case statement depending on what @processid equals.

My query syntax is wrong and want to know how it should be written.  Full SP attached
ALTER PROCEDURE [dbo].[SP_updatesalesjobentrytable]
 
@processid int,
@jobnumber int
 
AS
BEGIN
 
 
	SET NOCOUNT ON;
 
   
delete from dbo.Data_EmployeeTaskList where jobnumber = @jobnumber
and Step = @processid
 
Insert into  dbo.CompletedDataProcesses (processid,jobnumber)
   values (@processid,@jobnumber)
 
 
update sales_jobentry 
 
case 
when  @processid = 0 then set [0] = 1
when  @processid =  1 then set [1] = 1
when  @processid =  2 then set [2] = 1
when  @processid =  3 then set [3] = 1
when  @processid =  4 then set [4] = 1
when  @processid =  5 then set [5] = 1
when  @processid =  6 then set [6] = 1
when  @processid =  7 then set [7] = 1
when  @processid =  8 then set [8] = 1
end 
where jobnumber = @jobnumber
 
END
 
 
  

Open in new window

0
 

Accepted Solution

by:
logoncom earned 0 total points
ID: 22891153
Nevermind... I figured it out



If @processid = 0
 
BEGIN
	SET NOCOUNT ON;
 
update sales_jobentry
set [0] = 1 
where jobnumber = @jobnumber
 
 
END
 
ELSE
 
if @processid = 1
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [1] = 1
where jobnumber = @jobnumber
 
END
 
 
 
ELSE
 
if @processid = 2
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [2] = 2
where jobnumber = @jobnumber
 
END
 
ELSE
 
if @processid = 3
BEGIN
 
update Sales_JobEntry
set [3] = 1
where jobnumber = @jobnumber
	SET NOCOUNT ON;
END
 
ELSE
 
if @processid = 4
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [4] = 1
where jobnumber = @jobnumber
 
END
 
 
 
ELSE
 
if @processid = 5
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [5] = 1
where jobnumber = @jobnumber
 
END
 
 
 
 
 
 
 
 
ELSE
 
if @processid = 6
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [6] = 1
where jobnumber = @jobnumber
 
END
 
 
ELSE
 
if @processid = 7
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [7] = 1
where jobnumber = @jobnumber
 
END
 
 
 
ELSE
 
if @processid = 8
BEGIN
	SET NOCOUNT ON;
update Sales_JobEntry
set [8] = 1
where jobnumber = @jobnumber
 
END

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question