Atored Procdure help - 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

General SQL Server Forums

New to SQL Server Programming

Atored Procdure help

Author

Topic

Darkmatter5
Starting Member

17 Posts

Posted - 2012-10-04 : 13:48:27

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

38200 Posts

Posted - 2012-10-04 : 13:51:46

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

Darkmatter5
Starting Member

17 Posts

Posted - 2012-10-04 : 14:10:12

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-04 : 14:18:27

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

Darkmatter5
Starting Member

17 Posts

Posted - 2012-10-04 : 14:23:00

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-04 : 14:26:07

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources