Selecting a column OR an int - 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 2005 Forums

Transact-SQL (2005)

Selecting a column OR an int

Author

Topic

2639
Starting Member

6 Posts

Posted - 2007-02-19 : 15:33:54

I'm having a seriously hard time trying to figure this one out.

I have a stored proc that must do one of the following:
based on a varchar value, if that value exists, return the rowID of it. If it does NOT exist, create a new row and return the new rowID

I have the following code and I have not been able to get it to work. I get the error ' Syntax error converting the varchar value 'XXX' to a column of data type int.' ( XXX is the @invoiceNumber value I'm supplying )

Could someone help me figure out what I'm doing wrong? :D

[CODE] [dbo].[CreateNewInvoice]
(
@user varchar(50)
, @invoiceNumber varchar(50)
)

AS

DECLARE @ID int;

IF (( SELECT InventoryInvoiceNumber FROM InventoryInvoices WHERE InventoryInvoiceNumber = @invoiceNumber) > 0 )
--return the existing id #
BEGIN
SELECT InventoryInvoiceID FROM InventoryInvoices WHERE InventoryInvoiceNumber = @invoiceNumber
END

ELSE
--make an entry into the InventoryInvoice table first
BEGIN
INSERT INTO InventoryInvoices
(
InventoryInvoiceNumber
, UserWhoEntered
)
VALUES
(
@invoiceNumber
, @user
)
RETURN SCOPE_IDENTITY()

END[/CODE]

Andy

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-19 : 16:05:47

What is the data type of InventoryInvoiceNumber? It sounds like the error is saying it is int, in which case you need to change the data type of @invoiceNumber to int. You need to indicate exactly where in your code SQL Server is referring to with the error and also post the structure of the InventoryInvoices table.

Here's how you should do what you want, although you may need to change @invoiceNumber to int as I said.

ALTER PROCEDURE [dbo].[CreateNewInvoice]
(
	  @user varchar(50)
	, @invoiceNumber varchar(50)
)
AS
IF EXISTS (SELECT * FROM InventoryInvoices WHERE InventoryInvoiceNumber = @invoiceNumber)
--return the existing id #
BEGIN
	SELECT InventoryInvoiceID FROM InventoryInvoices WHERE InventoryInvoiceNumber = @invoiceNumber
END
ELSE
--make an entry into the InventoryInvoice table first
BEGIN
	INSERT INTO InventoryInvoices
	(
		InventoryInvoiceNumber
		, UserWhoEntered
	)
	VALUES
	(
		@invoiceNumber
		, @user
	)
	SELECT InventoryInvoiceID FROM InventoryInvoices WHERE InventoryInvoiceNumber = @invoiceNumber
END

2639
Starting Member

6 Posts

Posted - 2007-02-19 : 16:15:56

Thanks snSQL...I'll try your code out as soon as I post this. I did some debugging and discovered that the error doesnt happen when the InvoiceNumber is not present. It throws an error when it exists though ;)...The problem turns out to be in the 'IF' statement oddly enough.

As far as the data type of @invoiceNumber, it HAS to be varchar due to the business logic. An invoice number could contain letters as wel as numbers. It's misleading so I think I'll change the name of that at the very least :D:D

Thanks a lot..I'll post how it goes by using your suggestion above :D:D

Andy

2639
Starting Member

6 Posts

Posted - 2007-02-19 : 16:23:51

Yes sir! That did the trick! The only modification I took from you was the initial 'if' statement...I had to return Scope_identity() in the 'else' block because your suggestion was returning me zero as the value on the very first insertion. But, great job figuring out my incorrect 'if' statement..

I certainly appreciate the help very much!!!

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources