assign variable the value of insert

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)

assign variable the value of insert

Author

Topic

daprodigy
Starting Member

3 Posts

Posted - 2009-11-13 : 09:12:24

hello everybody

i think i have a really easy question ... but it's too hard for me

i have the following statement inside of a stored procedure:

INSERT INTO VG_RPP_ID VALUES ((SELECT TOP(1) RPP_ID FROM [PLVG].[dbo].[VG_RPP_COLLECTION] ORDER BY RPP_ID DESC) + 1)

No I would like to have the int value, which just was inserted into VG_RPP_ID, assigned to a local variable @id_ret.

Where do I have to place the part '@id_ret = ...'? I know it must be possible, but I don't know where ...

Thanks for the help in advance!

greets,
daprodigy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 09:25:46

declare @id_ret int
declare @sample table (id int)
insert into @sample select top 1 @id_ret= 4711 + 1
select * from @sample
select @id_ret
---------------------------
Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 09:35:43

INSERT INTO VG_RPP_ID
SELECT TOP(1) RPP_ID + 1 FROM [PLVG].[dbo].[VG_RPP_COLLECTION] ORDER BY RPP_ID DESC)

SELECT TOP(1) @id_ret=RPP_ID + 1 FROM [PLVG].[dbo].[VG_RPP_COLLECTION] ORDER BY RPP_ID DESC)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-13 : 09:40:45


DECLARE @Temp TABLE
(VG_RPP_ID INT)

INSERT INTO VG_RPP_ID INTO @Temp
OUTPUT INSERTED.VG_RPP_ID 
SELECT TOP(1) RPP_ID+1 
FROM [PLVG].[dbo].[VG_RPP_COLLECTION] 
ORDER BY RPP_ID DESCode

SELECT * FROM @Temp

daprodigy
Starting Member

3 Posts

Posted - 2009-11-13 : 10:19:51

ye ... obviously, it's not possible.

---------------------------------------------------------------
USE [PLVG]
GO
/****** Object: StoredProcedure [dbo].[GetNextRppId] Script Date: 11/13/2009 14:18:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetNextRppId]
AS
DECLARE @idret int;
DECLARE @i_count int;
DECLARE @i_rppcount int;

SELECT @i_count = COUNT(*) FROM VG_RPP_ID WITH(NOLOCK);
SELECT @i_rppcount = COUNT(*) FROM VG_RPP_COLLECTION WITH(NOLOCK);

IF (@i_count = 0)
BEGIN
IF (@i_rppcount = 0)
BEGIN
INSERT INTO VG_RPP_ID (RPP_ID) VALUES(1);
SET @idret = 1;
END
ELSE
BEGIN
INSERT INTO VG_RPP_ID SELECT TOP(1) @idret=RPP_ID + 1 FROM [PLVG].[dbo].[VG_RPP_COLLECTION] ORDER BY RPP_ID DESC
SELECT RPP_ID = @idret;
END
END
ELSE
BEGIN
UPDATE VG_RPP_ID SET @idret = RPP_ID = RPP_ID + 1 WHERE 1 = 1;
SELECT RPP_ID = @idret;
END
---------------------------------------------------------------
Msg 199, Level 15, State 1, Procedure GetNextRppId, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

daprodigy
Starting Member

3 Posts

Posted - 2009-11-13 : 12:04:17

i've splitted the whole functionality into 2 procedures.

thanks for the help!

daprodigy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources