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

Question

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

Solution 1

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

Solution 2

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

Solution 3

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

Comments

[Bulvak]

do I have to declare newreportID separately like you have or do I only add SET @newreportid.....

[Chris Moutray]

Did you really mean to duplicate my answer ;)

[Bulvak]

unfortunately the previosu programmers have not use proper data types and simple changes such as data type change are time consuming as my application is massive with 60+ sql tables and 60+ stored procedures...

[Bulvak]

unfortunately the previosu programmers have not use proper data types and simple changes such as data type change are time consuming as my application is massive with 60+ sql tables and 60+ stored procedures...

[MatBailie]

@mouters - You finished after I started. Concurrency issues ;)

[Bulvak]

can I do ALTER PROCEDURE [dbo].[AmendInsertDuplicateFields] (@ReportID varchar(50), @NewReportID varchar(50)) set @NewReportID= newid();

[Bulvak]

you guys make it so hard to choose the best answer because of all the good answers...

[Bulvak]

+1 for the detailed answer as well as alternatives to solution, good addition to question bank.

[Vadim Tychonoff]

declare it --> set it --> use it :)

[Bulvak]

Accepted as answer because of simple solution and luckily that select statement also answered a smaller mini question I had which was how to return the NewReportID value: SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { newGuidID = reader["ReportID"].ToString(); break; }

[Chris Moutray]

For returning the value, perhaps you might want to look at this stackoverflow.com/questions/290652/…

[Chris Moutray]

Basically set an out parameter in your sproc and use ExecuteNonQuery instead of ExecuteReader. Also SqlParameter that has an output direction...

[forsvarir]

@Nadal: "you guys make it so hard to choose the best answer because of all the good answers" - Have you considered up voting those answers that you consider good/useful (including this one)? You've asked 70 questions, and only ever up voted 16 times. Voting is free, you have up to 40 a day and it offers you the opportunity to reward peoples efforts where it's useful. Obviously you don't have to vote, it's optional, but it's maybe something to think about....

[dburges]

@Nadal, that is a teeny tiny db not a massive one. Our main db is only middling size and it has 950 tables and over 9000 stored procs and there are over 50 other databases that interact with it in various applications as well.

[Bulvak]

@forsvarir I have upvoted the helpful questions as I always do, always will. To "HLGEM", DAMNNNNN, thats hardcore....except issue was I am a student and soloing the entire project...code is 10-15ish years old.

[Bulvak]

Also @forsvarir, at times I will accept an answer and upvote another because I try to balance out the fairness...One person gets lots rep for having their answer selected as answer while another maybe just as good so in that situation I try to equal things out by selecting the best one as answer to give more rep and the other I + rep