sql-server - 如何使用 newid() 为存储过程变量赋值？

Question

我正在尝试生成一个新的 GUID 并将该值分配给 NewReportID .但是，我不确定是否替换 @NewReportID程序中随处可见 newid()它将为每一行生成一个新的 GUID。

我必须生成一个 GUID 并将 GUID 的值分配给 NewReportID 需要什么？

我在变量声明中尝试过:@NewReportID varchar(50)=newid()但这给了我很多错误。

ALTER PROCEDURE [dbo].[AmendInsertDuplicateFields] (@ReportID varchar(50), @NewReportID varchar(50))
AS

    Begin           

INSERT INTO [MVCOmar].[dbo].[PrideMVCCollisionBegin]
  ([ReportID], [LocalIncidentNum], [version], [MTOReferenceNo], [Submitted])
SELECT @NewReportID, [LocalIncidentNum], [version], [MTOReferenceNo], [Submitted]
FROM [MVCOmar].[dbo].[PrideMVCCollisionBegin] WHERE [ReportID]=@ReportID;

INSERT INTO [MVCOmar].[dbo].[PrideMVCCollisionDetails] ([Classification]      ,
    [ReportType]      ,[CollisionDate]      ,[CollisionDay]      ,
    [CollisionTime]      ,[CollisionLoc]      ,[ImpactLoc]      ,[ThruLaneNo]      ,
    [Weather1]      ,[Weather2]      ,[Light]      ,[TrafficControl]      ,
    [TrafficControlCond]      ,[RoadChar1]      ,[RoadChar2]      ,
    [RoadSurface1]      ,[RoadSurface2]      ,[RoadCond1]      ,[RoadCond2]      ,
    [RoadSurfaceCond1]      ,[RoadSurfaceCond2]      ,[RoadAlignment1]      ,
    [RoadAlignment2]      ,[RoadPavementMarking1]      ,[RoadPavementMarking2]      ,
    [OtherCollisionLoc]      ,[OtherImpactLoc]      ,[OtherWeather1]      ,
    [OtherWeather2]      ,[OtherLight]      ,[OtherTraffic]      ,
    [OtherRoadSurface1]      ,[OtherRoadSurface2]      ,[OtherRoadSurfaceCond1]      ,
    [OtherRoadSurfaceCond2]      ,[OtherClassification]      ,
    [DiagramDescription]      ,[R1NumLanes]      ,[R1MaxSpeed]      ,
    [R1AdviseSpeed]      ,[R2NumLanes]      ,[R2MaxSpeed]      ,[R2AdviseSpeed]      ,
    [NumInvolved]      ,[OfficerID]      ,[Checked]      ,[LastModified]      ,
    [LastModifiedBy]      ,[StartTime]      ,[EndTime]      ,[Display]      ,
    [ReportID]      ,[InitialImpactType]      ,[OtherInitialImpactType]      ,
    [SelfReported])
SELECT [Classification]      ,[ReportType]      ,[CollisionDate]      ,
    [CollisionDay]      ,[CollisionTime]      ,[CollisionLoc]      ,[ImpactLoc]      ,
    [ThruLaneNo]      ,[Weather1]      ,[Weather2]      ,[Light]      ,
    [TrafficControl]      ,[TrafficControlCond]      ,[RoadChar1]      ,
    [RoadChar2]      ,[RoadSurface1]      ,[RoadSurface2]      ,[RoadCond1]      ,
    [RoadCond2]      ,[RoadSurfaceCond1]      ,[RoadSurfaceCond2]      ,
    [RoadAlignment1]      ,[RoadAlignment2]      ,[RoadPavementMarking1]      ,
    [RoadPavementMarking2]      ,[OtherCollisionLoc]      ,[OtherImpactLoc]      ,
    [OtherWeather1]      ,[OtherWeather2]      ,[OtherLight]      ,
    [OtherTraffic]      ,[OtherRoadSurface1]      ,[OtherRoadSurface2]      ,
    [OtherRoadSurfaceCond1]      ,[OtherRoadSurfaceCond2]      ,
    [OtherClassification]      ,[DiagramDescription]      ,[R1NumLanes]      ,
    [R1MaxSpeed]      ,[R1AdviseSpeed]      ,[R2NumLanes]      ,[R2MaxSpeed]      ,
    [R2AdviseSpeed]      ,[NumInvolved]      ,[OfficerID]      ,[Checked]      ,
    [LastModified]      ,[LastModifiedBy]      ,[StartTime]       

    End

Answer 1

使用正确的数据类型？ uniqueidentifier

declare @NewReportID uniqueidentifier 
set @NewReportID = NEWID()

您使用的是什么风格的 sql-server？我试过你说的错误:

I have tried in the variable declaration: @NewReportID varchar(50)=newid() but that gave me lots of errors.

但这对我有用