Stored Procedure Errors - 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 2008 Forums

Transact-SQL (2008)

Stored Procedure Errors

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2011-09-27 : 08:31:09

I know very little about SQL. Below is a stored procedure that I would like to save, but I get errors. It was a long shot that it would work to begin with, but I need something that does this. Can anyone adjust my stored procedure to work?


CREATE PROCEDURE dbo.spSetBestPipeTPH 
	@pInsert BIT = FALSE,
	@pShift DATETIME = NULL,
	@pSize FLOAT = 0,
	@pGauge FLOAT = 0,
	@pTPH = FLOAT = 0,
	@pID = BIGINT = 0,
	@pSuccess BIT = TRUE	
AS
BEGIN
	SET @pID = (SELECT COUNT(*) FROM tblPipeHistory) + 1
	WHEN @pInsert = TRUE
		THEN 
			BEGIN
				INSERT INTO tblPipeHistory (ID, dtTimeStamp, PipeGauge, PipeDiameter, TPH) 
				VALUES (@pID, @pShift, @pGauge, @pDiameter, @pTPH)
			END
		ELSE
			BEGIN
				UPDATE tblPipeHistory
				SET dtTimeStamp = @pShift AND TPH = @pTPH
				WHERE PipeGauge = @pGauge AND PipeDiameter = @pSize
			END
	IF (SELECT TPH FROM tblPipeHistory WHERE TPH = @pTPH) IS NULL
	BEGIN
		SET @pSuccess = FALSE
	END	
END
	RETURN @pSuccess

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 08:45:26

This perhaps?


CREATE PROCEDURE dbo.spSetBestPipeTPH 
	@pInsert BIT = FALSE,
	@pShift DATETIME = NULL,
	@pSize FLOAT = 0,
	@pGauge FLOAT = 0,
	@pTPH = FLOAT = 0,
	@pID = BIGINT = 0,
	@pSuccess BIT = TRUE	
AS
BEGIN
	SELECT @pID = COUNT(*)+1 FROM tblPipeHistory
	IF @pInsert = TRUE
			BEGIN
				INSERT INTO tblPipeHistory (ID, dtTimeStamp, PipeGauge, PipeDiameter, TPH) 
				VALUES (@pID, @pShift, @pGauge, @pDiameter, @pTPH)
			END
		ELSE
			BEGIN
				UPDATE tblPipeHistory
				SET dtTimeStamp = @pShift,
					TPH = @pTPH
				WHERE PipeGauge = @pGauge AND PipeDiameter = @pSize
			END
	IF NOT EXISTS (SELECT * FROM tblPipeHistory WHERE TPH = @pTPH)
	BEGIN
		SET @pSuccess = FALSE
	END	

	RETURN @pSuccess

END

but there are a number of issues.

You are using the Parameters to create, and initialise, working variables. For example, the caller to this Sproc could pass "@pSuccess = FALSE" and your Sproc would never return TRUE. You can DECLARE additional variables within the body of the Sproc, and assign them an initial value.

Your use of COUNT(*) to get the "next ID" is very inefficient. Also, if a row is deleted then COUNT(*) will give you a value that matches an existing value (unless the highest ID row is deleted). You could use MAX(ID) instead of COUNT(*) or, better, set the ID column property to be IDENTITY and then ID will automatically be given the next available number (in that case EXCLUDE it from the INSERT statement altogether)

You could set @pSuccess based on whether the @@ROWCOUNT after the INSERT or UPDATE was = 1:

IF @@ROWCOUNT <> 1 SELECT @pSuccess = FALSE

you must do that IMMEDIATELY after the statement it refers to, so put that after BOTH the INSERT and UPDATE statements.

MKz71
Starting Member

30 Posts

Posted - 2011-09-27 : 09:33:56

I see what you mean... I don't plan on passing success just returning. What if I declare it right above SELECT @pID = COUNT(*)+1 FROM tblPipeHistory?

MKz71
Starting Member

30 Posts

Posted - 2011-09-27 : 09:51:19

I get the following error:
[url]http://kti-online.net/error.jpg[/url]
Any Ideas?

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 10:46:45

"What if I declare it right above ..."

I would do it like this:


CREATE PROCEDURE dbo.spSetBestPipeTPH 
	@pInsert BIT = FALSE,
	@pShift DATETIME = NULL,
	@pSize FLOAT = 0,
	@pGauge FLOAT = 0,
	@pTPH FLOAT = 0
AS
BEGIN
DECLARE	@pID BIGINT = 0,
	@pSuccess BIT = TRUE	
...

Your error is that you ahve too many "=" so


DECLARE @xxx = INT = 0

should be


DECLARE @xxx INT = 0

MKz71
Starting Member

30 Posts

Posted - 2011-09-27 : 11:56:10

OK Thank you for your help. This saves and now I am testing. Thanks again.

MKz71
Starting Member

30 Posts

Posted - 2011-09-27 : 15:52:04

Ok this does not seem to be working. I passed arguments through from my code and nothing. I have no clue, and I was wondering if you think the stored proc should even execute at all?


ALTER PROCEDURE dbo.spSetBestPipeTPH 
	@pInsert BIT = 0,
	@pShift DATETIME = NULL,
	@pSize FLOAT = 0,
	@pGauge FLOAT = 0,
	@pTPH FLOAT = 0
AS
BEGIN
	DECLARE @pID BIGINT,
			@pSuccess BIT
			
	SET @pID = 0
	SET @pSuccess = 1
			
		
	SELECT @pID = MAX(ID)+1 FROM tblPipeHistory
	IF @pInsert = 1
		BEGIN
			INSERT INTO tblPipeHistory (ID, dtTimeStamp, PipeGauge, PipeDiameter, TPH) 
			VALUES (@pID, @pShift, @pGauge, @pSize, @pTPH)

			IF NOT EXISTS (SELECT * FROM tblPipeHistory WHERE TPH = @pTPH AND PipeGauge = @pGauge AND PipeDiameter = @pSize)
				BEGIN
					SET @pSuccess = 0
				END
		END
	ELSE
		BEGIN
			UPDATE tblPipeHistory
			SET dtTimeStamp = @pShift, TPH = @pTPH
			WHERE PipeGauge = @pGauge AND PipeDiameter = @pSize
			
			IF (SELECT TPH FROM tblPipeHistory WHERE PipeGauge = @pGauge AND PipeDiameter = @pSize) != @pTPH
				BEGIN
					SET @pSuccess = 0
				END
		END
	RETURN @pSuccess
END

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 19:24:34

"I passed arguments through from my code and nothing"

Looks OK to me. I think your test for Success is excessive - in the sense that if the INSERT or UPDATE fails a fatal error will be generated, or failing that an error state (which you can check for).

When you say "nothing", how do you mean? This Sproc should insert a row, or update an existing row, does it not do that?

MKz71
Starting Member

30 Posts

Posted - 2011-09-28 : 06:54:49

No it doesn't insert or update. I know I am passing the arguments correctly because I have worked with passing arguments to stored procedures in my code for some time now, I can remove the check no problem. Does this mean I don't return anything? I only put the check in there for a temporary error check to see if it worked or not.

Kristen
Test

22859 Posts

Posted - 2011-09-29 : 03:24:44

Well I haven't actually run this, so I can't test how it might work, but reading the code it will either

1) Insert
2) Update
3) Error

There are no alternatives. Are you 100% sure that the data is not inserting / updating?

You could put some PRINT statements in the code so you can see what is going on:


ALTER PROCEDURE dbo.spSetBestPipeTPH 
	@pInsert BIT = 0,
	@pShift DATETIME = NULL,
	@pSize FLOAT = 0,
	@pGauge FLOAT = 0,
	@pTPH FLOAT = 0
AS
BEGIN
	DECLARE @pID BIGINT,
			@pSuccess BIT
			
	SET @pID = 0
	SET @pSuccess = 1
			
		
	SELECT @pID = MAX(ID)+1 FROM tblPipeHistory
PRINT 'Start'
	IF @pInsert = 1
		BEGIN
PRINT 'Do INSERT'
			INSERT INTO tblPipeHistory (ID, dtTimeStamp, PipeGauge, PipeDiameter, TPH) 
			VALUES (@pID, @pShift, @pGauge, @pSize, @pTPH)

			IF NOT EXISTS (SELECT * FROM tblPipeHistory WHERE TPH = @pTPH AND PipeGauge = @pGauge AND PipeDiameter = @pSize)
				BEGIN
					SET @pSuccess = 0
				END
		END
	ELSE
		BEGIN
PRINT 'Do UPDATE'
			UPDATE tblPipeHistory
			SET dtTimeStamp = @pShift, TPH = @pTPH
			WHERE PipeGauge = @pGauge AND PipeDiameter = @pSize
			
			IF (SELECT TPH FROM tblPipeHistory WHERE PipeGauge = @pGauge AND PipeDiameter = @pSize) != @pTPH
				BEGIN
					SET @pSuccess = 0
				END
		END
PRINT 'RETURN'
	RETURN @pSuccess
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources