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

What flavour of sql-server are you using? – Chris Moutray Jun 27 '12 at 12:18 — Chris Moutray, Jun 27 '12 at 12:18

Chris Moutray · Accepted Answer · 2012-06-27T12:22:39.203

4

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

edited Jun 27 '12 at 12:22

answered Jun 27 '12 at 12:14

Chris Moutray

18,029
7
45
66

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 Jun 27 '12 at 12:16
can I do ALTER PROCEDURE [dbo].[AmendInsertDuplicateFields] (@ReportID varchar(50), @NewReportID varchar(50)) set @NewReportID= newid(); – Bulvak Jun 27 '12 at 12:22
you guys make it so hard to choose the best answer because of all the good answers... – Bulvak Jun 27 '12 at 12:29
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; } – Bulvak Jun 27 '12 at 12:31
For returning the value, perhaps you might want to look at this http://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net – Chris Moutray Jun 27 '12 at 12:41
Basically set an `out` parameter in your sproc and use `ExecuteNonQuery` instead of `ExecuteReader`. Also SqlParameter that has an output direction... – Chris Moutray Jun 27 '12 at 12:42
1

@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.... – forsvarir Jun 27 '12 at 13:02
@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. – HLGEM Jun 27 '12 at 17:12
@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 Jun 27 '12 at 18:08
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 – Bulvak Jun 27 '12 at 18:11

MatBailie · Answer 2 · 2012-06-27T12:23:02.693

3

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

edited Jun 27 '12 at 12:23

answered Jun 27 '12 at 12:15

MatBailie

83,401
18
103
137

Did you really mean to duplicate my answer ;) – Chris Moutray Jun 27 '12 at 12:16
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 Jun 27 '12 at 12:17
@mouters - You finished after I started. Concurrency issues ;) – MatBailie Jun 27 '12 at 12:17
+1 for the detailed answer as well as alternatives to solution, good addition to question bank. – Bulvak Jun 27 '12 at 12:30

score 2 · Answer 3 · answered Jun 27 '12 at 12:10

2

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

answered Jun 27 '12 at 12:10

Vadim Tychonoff

801
5
7

do I have to declare newreportID separately like you have or do I only add SET @newreportid..... – Bulvak Jun 27 '12 at 12:15
declare it --> set it --> use it :) – Vadim Tychonoff Jun 27 '12 at 12:31

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

3 Answers3