ALTER PROCEDURE [dbo].[spTelling]
	-- Add the parameters for the stored procedure here
	@scannummer Nvarchar(13),	
	@outscan Nvarchar(13) 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
	declare @productid int, @unique int, @un int

select @productid = (productid) From Product Where scannummer = @scannummer

select @unique = count(productid) from Product Where scannummer = @scannummer

if (@unique >  0)
		begin
			select @un = count(productid) from Telling where ProductId = @productid
if (@un > 0)
		begin
		Update TELLING set Tal = Tal + 1 where ProductId = @productid
		end
		
if (@un = 0)
		begin
		INSERT INTO Telling (ProductID, Tal, Date) VALUES 			     (@productid,1,getdate())
		end
		end
if (@unique = 0)
Begin

set @outscan =   @scannummer
return @outscan 
EXEC dbo.spTelling @scannummer, @outscan OUTPUT
END

END

if (@un = 0)
		begin
		INSERT INTO Telling (ProductID, Tal, Date) VALUES 			     (@productid,1,getdate())
		end
		end

quote:

---

Originally posted by JohnDW


message from management studio:
Procedure or function 'spTelling' expects parameter '@scannummer', which was not supplied.

Question:
If the scannummer is not found in the table,
how can I get @scannummer from the sp in my vb.net application.
@scannummer is a string.

---

if (@unique = 0)
Begin

set @outscan =   @scannummer
return @outscan 
EXEC dbo.spTelling @scannummer, @outscan OUTPUT
END

ALTER PROCEDURE	dbo.spTelling
(
	@ScanNummer NVARCHAR(13),
	@CurrentTal INT OUT
)
AS

-- Prevent unwanted resulsets back to client
SET NOCOUNT ON;

-- Prepare output value
DECLARE	@Return TABLE
	(
		Value INT NOT NULL
	);

-- Increment the Telling Tal
MERGE	dbo.Telling AS tgt
USING	(
		SELECT TOP(1)	ProductID,
				1 AS Tal,
				GETDATE() AS [Date]
 		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]
			)
			(
				src.ProductID,
				src.Tal,
				src.[Date]
			)
OUTPUT	inserted.Tal
INTO	@Return
	(
		Value
	);

-- Return the current Tal for ScanNummer
SELECT	@CurrentTal = Value
FROM	@Return;

ALTER PROCEDURE	dbo.spTelling
(
	@ScanNummer NVARCHAR(13),
	@CurrentTal INT OUT
)
AS
-- Prevent unwanted resulsets back to client
SET NOCOUNT ON;
-- Prepare output value
DECLARE	@Return TABLE
	(
		Value INT NOT NULL
	);
-- Increment the Telling Tal
MERGE	dbo.Telling AS tgt
USING	(
		SELECT TOP(1)	ProductID,
				1 AS Tal,
				GETDATE() AS [Date]
 		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]
			) values
			(
				src.ProductID,
				src.Tal,
				src.[Date]
			)
OUTPUT	inserted.Tal
INTO	@Return
	(
		Value
	);

SELECT 

-- If @Return(Value) is null (means that there's no scannummer in the table -- product, then return @scannummer
-- else (if @Return has a Value: end ( return nothing )
@CurrentTal = ISNULL(Value, CAST(@Scannummer AS INT))



FROM	@Return;

SET @CurrentTal = (
		SELECT TOP (1) value
		FROM @Return
		WHERE value IS NOT NULL
		ORDER BY...
		)

@CurrentTal = ISNULL(Value, CAST(@Scannummer AS INT))
or with COALESCE(Value, CAST(@Scannummer AS INT))