CODEWITHSUNDEEP
sql-servert-sqlnewid
Bulvak
Bulvak

Reputation: 1804

How to use newid() to assign a value to a stored procedure variable?

I am trying to generate a new GUID and assign that value to NewReportID. But, I am unsure that if I replace @NewReportID everywhere in the procedure by newid() that it will generate a new GUID for each line.

What do I have to generate just one GUID and assign NewReportID the value of the GUID?

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

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

Upvotes: 4

Views: 14411

Answers (3)

MatBailie
MatBailie

Reputation: 86735

Is there a reason you are using a VARCHAR()?

Ideally you shound be using the uniqueidentifier type.

DECLARE
  @myGUID    uniqueidentifier
SELECT
  @myGUID    = NewID()


EDIT

As you are suck with a VARCHAR(), you can settle for converting it to a string. It's more a waste of space than any real technical problem...

DECLARE
  @myGUID    VARCHAR(50)
SELECT
  @myGUID    = CONVERT(varchar(50), @myid)

In your SP, @newReportID is an input parameter. So you can either change you SP to not need the input, or set it before the input


Using the current SP...

DECLARE
  @myGUID    VARCHAR(50)
SELECT
  @myGUID    = CONVERT(varchar(50), @myid)

EXEC [dbo].[AmendInsertDuplicateFields] @reportID, @myGUID


Changing the SP...

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

DECLARE
  @NewReportID VARCHAR(50)
SELECT
  @NewReportID = CONVERT(varchar(50), @myid)

-- Then use @NewReportID onwards in the SP

And call the SP with just one parameter...

EXEC [dbo].[AmendInsertDuplicateFields] @reportID

http://msdn.microsoft.com/en-us/library/ms190348.aspx

Upvotes: 3

Chris Moutray
Chris Moutray

Reputation: 18379

Use the correct data type? uniqueidentifier

declare @NewReportID uniqueidentifier 
set @NewReportID = NEWID()

What flavour of sql-server are you using? I tried what you said was erroring:

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

But this works for me

enter image description here

Upvotes: 4

Vadim Tychonoff
Vadim Tychonoff

Reputation: 881

   DECLARE @NewReportID VARCHAR(50)
   SET @NewReportID = cast(NEWID() AS VARCHAR(50))

Upvotes: 2

Related Questions