b*f
发帖数: 3154 | 1 这么一个例子：
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1;
GO
CREATE TABLE T1 (id int PRIMARY KEY, timestamp);
GO
INSERT INTO T1(id) VALUES (1);
GO
declare @v timestamp;
INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10);
select @v
假设这个@v是俺想作为stored-procedure的OUT参数返回，怎样才能把inserted.
timestamp值传递给@v呢？
多谢，给俺解决问题的奉上双黄包一个... |
g***l
发帖数: 18555 | 2 传INSERTED的值是要用TRIGGER的吧，不知道你传个这个是干什么用的 |
i****a
发帖数: 36252 | 3 DECLARE @v TABLE(result VARBINARY(8)) --note: OUTPUT is table
INSERT INTO T1(id)
OUTPUT inserted.[timestamp]
INTO @v
VALUES (2)
SELECT result
FROM @v
【在 b*f 的大作中提到】
: 这么一个例子：
: IF OBJECT_ID('T1') IS NOT NULL
: DROP TABLE T1;
: GO
: CREATE TABLE T1 (id int PRIMARY KEY, timestamp);
: GO
: INSERT INTO T1(id) VALUES (1);
: GO
: declare @v timestamp;
: INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10);
|
i****a
发帖数: 36252 | 4 and if you are only processing 1 single ID at a time, just
DECLARE @v VARBINARY(8)
SELECT @v = timestamp
FROM T1
WHERE id = 10
【在 b*f 的大作中提到】
: 这么一个例子：
: IF OBJECT_ID('T1') IS NOT NULL
: DROP TABLE T1;
: GO
: CREATE TABLE T1 (id int PRIMARY KEY, timestamp);
: GO
: INSERT INTO T1(id) VALUES (1);
: GO
: declare @v timestamp;
: INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10);
|
b*f
发帖数: 3154 | 5 I am trying to avoid an extra select statement for performance concern. Any
other ways?
Thanks.
【在 i****a 的大作中提到】
: and if you are only processing 1 single ID at a time, just
: DECLARE @v VARBINARY(8)
: SELECT @v = timestamp
: FROM T1
: WHERE id = 10
|
i****a
发帖数: 36252 | 6 then see my 1st post
select the single line result into your SP's output variable.
Any
【在 b*f 的大作中提到】
: I am trying to avoid an extra select statement for performance concern. Any
: other ways?
: Thanks.
|
b*f
发帖数: 3154 | 7 Is a temp table more overhead...?
【在 i****a 的大作中提到】
: then see my 1st post
: select the single line result into your SP's output variable.
:
: Any
|
i****a
发帖数: 36252 | 8 if you have a single line insert, then it's just a 1 record table
variable. table variable has a lighter overhead than a temp table.
as for method 1 vs method 2 I posted, I think method 1 is more efficient
as it doesn't require a select from T1. if you T1 has large number of rows
and not indexed.
check the query execution plan against your server. you'll probably have
to do some load testing to find out which one works better for your real
application.
【在 b*f 的大作中提到】
: Is a temp table more overhead...?
|
a9
发帖数: 21638 | 9 看了下面的讨论，为什么不想个变通的法子？还有想想你取出来干啥用？
难道你要用timestamp做索引吗？
【在 b*f 的大作中提到】
: 这么一个例子：
: IF OBJECT_ID('T1') IS NOT NULL
: DROP TABLE T1;
: GO
: CREATE TABLE T1 (id int PRIMARY KEY, timestamp);
: GO
: INSERT INTO T1(id) VALUES (1);
: GO
: declare @v timestamp;
: INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10);
|
