Simple insert select update question

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)

Simple insert select update question

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-07-08 : 18:01:11

Hi all,

i'm trying to insert a new record with all external values, and one value form another table.
then update the record that i selected form the form the external table and return it in an output parameter

this is what i came up with, but doesnt work

ALTER PROCEDURE dbo.SP_Order_Add
@orderid As Int,
@name As nvarchar(100),
@address As nvarchar(300),
@phone nvarchar(30),
@email nvarchar(100),
@orderinfo nvarchar(200),
@shopname nvarchar(50),
@value nvarchar(10),
@easyref int output
AS
SET NOCOUNT ON

INSERT INTO dbo.T_OrderInfo
(
orderid ,
[name] ,
address ,
phone ,
email ,
orderinfo ,
easyref ,
shopname ,
value
)
VALUES

(
@orderid ,
@name ,
@address ,
@phone ,
@email ,
@orderinfo ,
( Select top 1 ref from T_refrences where [free]=0 ) ,
@shopname ,
@value
)

select @easyref=easyref from T_OrderInfo where id=@@identity

update T_refrences set [free]=1 where ref=@easyref

RETURN

thanks a lot,

Mike

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-08 : 21:23:16

quote:
select @easyref=easyref from T_OrderInfo where id=@@identity

DECLARE @sql varchar(500)
SET @easyref = quotename(@easyref)
SET @sql = 'SELECT ' + @easyref + '= easyref from T_OrderInfro where id =' + @@identity
EXEC (@sql)

Hope this work for you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 21:39:17

[code]
INSERT INTO dbo.T_OrderInfo
(
orderid ,
[name] ,
address ,
phone ,
email ,
orderinfo ,
easyref ,
shopname ,
value
)
SELECT TOP 1
@orderid ,
@name ,
@address ,
@phone ,
@email ,
@orderinfo ,
t.ref
@shopname ,
@value
FROM T_refrences t
WHERE t.[free] = 0
ORDER BY t.ref
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mike13
Posting Yak Master

219 Posts

Posted - 2009-07-09 : 04:59:24

thanks, that helped ;-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources