Update table with tinyint in stored procedure

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

Update table with tinyint in stored procedure

Author

Topic

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 15:14:19

I've got a sp that goes well.
except for the tinyint value, that doesn't update in the table.


ALTER PROCEDURE	[dbo].[spTelling]
(
	@ScanNummer NVARCHAR(13),
	@Basis tinyint,
	@CurrentTal INT OUT
)
AS
-- Prevent unwanted resulsets back to client
SET NOCOUNT ON;
-- Prepare output value
DECLARE	@Return TABLE
	(
		Value INT NOT NULL
	);
MERGE	dbo.Telling AS tgt
USING	(
		SELECT TOP(1)	ProductID,
				1 AS Tal, 
				GETDATE() AS [Date],@Basis as Basis
 		FROM		dbo.Product
		WHERE		ScanNummer = @ScanNummer
	) AS src ON src.ProductID = tgt.ProductID
WHEN	MATCHED
	THEN	UPDATE
		SET	tgt.Tal += src.Tal 
WHEN	NOT MATCHED BY TARGET
	THEN	INSERT	(
				ProductID,
				Tal, 
				[Date], Basis
			) values
			(
				src.ProductID,
				src.Tal,
				src.[Date], src.Basis
			)
OUTPUT	inserted.Tal
INTO	@Return	(
		Value
	);
SELECT 
@CurrentTal = ISNULL(Value, CAST(@Scannummer AS INT))
FROM	@Return;

The insert statement works. But
I want also to update Basis (tinyint) in the table Telling.


WHEN	MATCHED
	THEN	UPDATE
		SET	tgt.Tal += src.Tal

Where must I write
set basis = @Basis

if you do not try, it will not work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-17 : 15:20:11

"+="?

Maybe you want this: Tal = tgt.Tal + src.Tal

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 15:30:07

I think i'm not clear.

The SP updates or insert a new record to the table Telling.

When the SP inserts a new row in the column it writes
all the columns (T_ID, Productid, Tal, Date,Basis). That's going good.

The SP as it is now updates only the column (Tal).
I want also that the SP updates the column Basis.

Hope i've made it clear.

Txs.

if you do not try, it will not work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-17 : 15:37:24

Sorry I misunderstood and thought you were saying the update wasn't working.

UPDATE SET tgt.Tal += src.Tal, tgt.Basis = @Basis

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 15:44:50

Bingo!

Txs.

John.

if you do not try, it will not work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-17 : 15:46:11

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources