SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Atored Procdure help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Darkmatter5
Starting Member

17 Posts

Posted - 10/04/2012 :  13:48:27  Show Profile  Reply with Quote
Here's my SP code:


USE [byrndb]
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateJob]    Script Date: 10/04/2012 12:31:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:
-- Create date: October 3, 2012
-- Description:	Update a job
-- =============================================
ALTER PROCEDURE [dbo].[sp_UpdateJob] 
	-- Add the parameters for the stored procedure here
	@ID int = 0,
	@JobNumber		varchar(20) = NULL,
	@Description	varchar(MAX) = NULL,
	@Location		varchar(MAX) = NULL,
	@SubdivisionID	int = 1,
	@Section		varchar(50) = NULL,
	@LotBlk			varchar(8) = NULL,
	@FbPg			varchar(9) = NULL,
	@MemoInfo		varchar(MAX) = NULL,
	@EmployeeID		int = 1,
	@VolPg			varchar(9) = NULL,
	@Estimate		decimal(10,2) = 0.00,
	@Amount			decimal(10,2) = 0.00,
	@FndDate		datetime2(7) = NULL,
	@AssignDate		datetime2(7) = NULL,
	@CompletionDate	datetime2(7) = NULL,
	@result int = 0 output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS (SELECT * FROM jobs WHERE JobID = @ID)
	  BEGIN
	    -- Check for change and update JobNumber
	    IF (SELECT JobNumber FROM jobs WHERE JobID = @ID) != @JobNumber
	      BEGIN
	        UPDATE jobs SET JobNumber = @JobNumber WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Description
	    IF (SELECT JobDescription FROM jobs WHERE JobID = @ID) != @Description
	      BEGIN
	        UPDATE jobs SET JobDescription = @Description WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Location
	    IF (SELECT JobLocation FROM jobs WHERE JobID = @ID) != @Location
	      BEGIN
	        UPDATE jobs SET JobLocation = @Location WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Subdivision
	    IF (SELECT SubdivisionID FROM jobs WHERE JobID = @ID) != @SubdivisionID
	      BEGIN
	        UPDATE jobs SET SubdivisionID = @SubdivisionID WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Section
	    IF (SELECT JobSection FROM jobs WHERE JobID = @ID) != @Section
	      BEGIN
	        UPDATE jobs SET JobSection = @Section WHERE JobID = @ID
	      END
	      
	    -- Check for change and update LotBlk
	    IF (SELECT JobLot_blk FROM jobs WHERE JobID = @ID) != @LotBlk
	      BEGIN
	        UPDATE jobs SET JobLot_blk = @LotBlk WHERE JobID = @ID
	      END
	      
	    -- Check for change and update FbPg
	    IF (SELECT JobFb_pg FROM jobs WHERE JobID = @ID) != @FbPg
	      BEGIN
	        UPDATE jobs SET JobFb_pg = @FbPg WHERE JobID = @ID
	      END
	      
	    -- Check for change and update MemoInfo
	    IF (SELECT JobMemo_info FROM jobs WHERE JobID = @ID) != @MemoInfo
	      BEGIN
	        UPDATE jobs SET JobMemo_info = @MemoInfo WHERE JobID = @ID
	      END
	      
	    -- Check for change and update Employee
	    IF (SELECT EmployeeID FROM jobs WHERE JobID = @ID) != @EmployeeID
	      BEGIN
	        UPDATE jobs SET EmployeeID = @EmployeeID WHERE JobID = @ID
	      END
	      
	    -- Check for change and update VolPg
	    IF (SELECT JobVol_pg FROM jobs WHERE JobID = @ID) != @VolPg
	      BEGIN
	        UPDATE jobs SET JobVol_pg = @VolPg WHERE JobID = @ID
	      END
	    
	    -- Check for change and update Estimate
	    IF (SELECT JobEstimate FROM jobs WHERE JobID = @ID) != @Estimate
	      BEGIN
	        UPDATE jobs SET JobEstimate = @Estimate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update Amount
	    IF (SELECT JobAmount FROM jobs WHERE JobID = @ID) != @Amount
	      BEGIN
	        UPDATE jobs SET JobAmount = @Amount WHERE JobID = @ID
	      END
	    
	    -- Check for change and update FndDate
	    IF (SELECT JobFnd_date FROM jobs WHERE JobID = @ID) != @FndDate
	      BEGIN
	        UPDATE jobs SET JobFnd_date = @FndDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update AssignDate
	    IF (SELECT JobAssign_date FROM jobs WHERE JobID = @ID) != @AssignDate
	      BEGIN
	        UPDATE jobs SET JobAssign_date = @AssignDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update CompletionDate
	    IF (SELECT JobCompletion_date FROM jobs WHERE JobID = @ID) != @CompletionDate
	      BEGIN
	        UPDATE jobs SET JobCompletion_date = @CompletionDate WHERE JobID = @ID
	      END
	    
	    SET @result = 1
	  END
	ELSE BEGIN SET @result = 0 END
END

RETURN @result


If I run this SP and supply data for every parameter except "result", but change one, the changed parameter doesn't get applied and the result is 0. Any ideas?

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 10/04/2012 :  13:51:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
You'll need to be more explicit. Show us your EXEC command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Darkmatter5
Starting Member

17 Posts

Posted - 10/04/2012 :  14:10:12  Show Profile  Reply with Quote
Here's was what executing the SP generated for me.


USE [byrndb]
GO

DECLARE	@return_value int,
		@result int

EXEC	@return_value = [dbo].[sp_UpdateJob]
		@ID = 6692,
		@JobNumber = N'99999',
		@Description = N'NOTHING',
		@Location = N'something',
		@SubdivisionID = 90,
		@Section = N'2',
		@LotBlk = N'2/2',
		@FbPg = N'2/2',
		@MemoInfo = N'NOTHING',
		@EmployeeID = 21,
		@VolPg = N'2/2',
		@Estimate = 2.00,
		@Amount = 2.00,
		@FndDate = 2012-10-03 00:00:00.00,
		@AssignDate = 2012-10-03 00:00:00.00,
		@CompletionDate = 2012-10-03 00:00:00.00,
		@result = @result OUTPUT

SELECT	@result as N'@result'

SELECT	'Return Value' = @return_value

GO


It generated the following error "Msg 102, Level 15, State 1, Line 19 Incorrect syntax near '-'."
It turns out it needed "N''" around each of the date values and then it ran fine, but how can I fix this?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 10/04/2012 :  14:18:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't need the N's because you are using varchar, but you do need to put single quotes around the date/time data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Darkmatter5
Starting Member

17 Posts

Posted - 10/04/2012 :  14:23:00  Show Profile  Reply with Quote
So in the SP code would I change the following lines:


-- Check for change and update FndDate
	    IF (SELECT JobFnd_date FROM jobs WHERE JobID = @ID) != @FndDate
	      BEGIN
	        UPDATE jobs SET JobFnd_date = @FndDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update AssignDate
	    IF (SELECT JobAssign_date FROM jobs WHERE JobID = @ID) != @AssignDate
	      BEGIN
	        UPDATE jobs SET JobAssign_date = @AssignDate WHERE JobID = @ID
	      END
	    
	    -- Check for change and update CompletionDate
	    IF (SELECT JobCompletion_date FROM jobs WHERE JobID = @ID) != @CompletionDate
	      BEGIN
	        UPDATE jobs SET JobCompletion_date = @CompletionDate WHERE JobID = @ID
	      END


to:


-- Check for change and update FndDate
	    IF (SELECT JobFnd_date FROM jobs WHERE JobID = @ID) != @FndDate
	      BEGIN
	        UPDATE jobs SET JobFnd_date = '@FndDate' WHERE JobID = @ID
	      END
	    
	    -- Check for change and update AssignDate
	    IF (SELECT JobAssign_date FROM jobs WHERE JobID = @ID) != @AssignDate
	      BEGIN
	        UPDATE jobs SET JobAssign_date = '@AssignDate' WHERE JobID = @ID
	      END
	    
	    -- Check for change and update CompletionDate
	    IF (SELECT JobCompletion_date FROM jobs WHERE JobID = @ID) != @CompletionDate
	      BEGIN
	        UPDATE jobs SET JobCompletion_date = '@CompletionDate' WHERE JobID = @ID
	      END


Is that correct?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 10/04/2012 :  14:26:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
No.


EXEC	@return_value = [dbo].[sp_UpdateJob]
		@ID = 6692,
		@JobNumber = '99999',
		@Description = 'NOTHING',
		@Location = 'something',
		@SubdivisionID = 90,
		@Section = '2',
		@LotBlk = '2/2',
		@FbPg = '2/2',
		@MemoInfo = 'NOTHING',
		@EmployeeID = 21,
		@VolPg = '2/2',
		@Estimate = 2.00,
		@Amount = 2.00,
		@FndDate = '2012-10-03 00:00:00.00',
		@AssignDate = '2012-10-03 00:00:00.00',
		@CompletionDate = '2012-10-03 00:00:00.00',
		@result = @result OUTPUT


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000