我在我的一个Sql (2008)存储过程中有以下代码,它可以很好地执行:
CREATE PROCEDURE [dbo].[Item_AddItem]
@CustomerId uniqueidentifier,
@Description nvarchar(100),
@Type int,
@Username nvarchar(100),
AS
BEGIN
DECLARE @TopRelatedItemId uniqueidentifier;
SET @TopRelatedItemId =
(
SELECT top(1) RelatedItemId
FROM RelatedItems
WHERE CustomerId = @CustomerId
)
DECLARE @TempItem TABLE
(
ItemId uniqueidentifier,
CustomerId uniqueidentifier,
Description nvarchar(100),
Type int,
Username nvarchar(100),
TimeStamp datetime
);
INSERT INTO Item
OUTPUT INSERTED.* INTO @TempItem
SELECT NEWID(), @CustomerId, @Description, @Type, @Username, GETDATE()
SELECT
ItemId,
CustomerId,
@TopRelatedItemId,
Description,
Type,
Username,
TimeStamp
FROM
@TempItem
END
GO
所以你们的问题是,有没有可能做一些类似的事情:
DECLARE @TempCustomer TABLE
(
CustomerId uniqueidentifier,
FirstName nvarchar(100),
LastName nvarchar(100),
Email nvarchar(100)
);
SELECT
CustomerId,
FirstName,
LastName,
Email
INTO
@TempCustomer
FROM
Customer
WHERE
CustomerId = @CustomerId
这样我就可以在下面的语句中重用内存中的数据了吗?SQL Server使用了上面的语句,但是我不想创建单独的变量,并通过单独的SELECT语句对同一个表初始化每个变量。啊!
关于如何在不对同一个表进行多个查询的情况下实现某些功能,有什么建议吗?
发布于 2011-01-28 09:34:12
您不能选择..变成..。一个表变量。最好的做法是先创建它,然后再插入其中。你的第二个片段必须是
DECLARE @TempCustomer TABLE
(
CustomerId uniqueidentifier,
FirstName nvarchar(100),
LastName nvarchar(100),
Email nvarchar(100)
);
INSERT INTO
@TempCustomer
SELECT
CustomerId,
FirstName,
LastName,
Email
FROM
Customer
WHERE
CustomerId = @CustomerId
发布于 2011-08-31 00:35:26
如果你想简单地分配一些变量供以后使用,你可以一下子完成它们,如下所示:
declare @var1 int,@var2 int,@var3 int;
select
@var1 = field1,
@var2 = field2,
@var3 = field3
from
table
where
condition
如果这是你想要的类型
发布于 2011-01-30 08:45:04
您可以这样做:
SELECT
CustomerId,
FirstName,
LastName,
Email
INTO #tempCustomer
FROM
Customer
WHERE
CustomerId = @CustomerId
然后,稍后
SELECT CustomerId FROM #tempCustomer
不需要声明#tempCustomer的结构
https://stackoverflow.com/questions/4823880
复制相似问题