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/
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

JohnDW
Starting Member

45 Posts

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

Txs.

John.

if you do not try, it will not work
Go to Top of Page

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/
Go to Top of Page
   

- Advertisement -