Solved

GUID generation in Stored Prcoedure

Posted on 2008-10-10
9
638 Views
Last Modified: 2012-05-05
I need a SQL Server Stored Procedure to fetch GUID. I will be using this Procedure in ETL Tool (Informatica).
0
Comment
Question by:ManikandanN
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685726
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22685728
SELECT NEWID()
0
 

Author Comment

by:ManikandanN
ID: 22685799
I am looking for Stored Procedure, not the built in function. Or any alternative to fetch the output of newid() in a stoed procedure will also do.

Thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685842
can you please clarify the context? ie what exactly you are trying to achieve?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ManikandanN
ID: 22685885
We are synchronizing SQL Server DB to DB2 database.
GUID is expected to be populated for any new record created in DB2 database. No UUID or anyother DB2 method could be used.
Output should be exactly as SQL Server GUID output. (ie same as SELECT newid())

In our tool we have option to read from SQL Server DB and to write to the target DB2, inorder to populate the column in DB2 with GUID new value I need a stored procedure.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 125 total points
ID: 22685933
inside the sp do this

declare @unid uniqueidentifier
select @Unid = newid()

and use the above @UnId instead of NewID() for your INSERT statement
and you can return the @unid either using a select statement or output variable
0
 

Author Comment

by:ManikandanN
ID: 22686048
Sorry, I am novice, could you pls. help me in substituting these in the template

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
      -- Add the parameters for the stored procedure here
      <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
      <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
0
 

Author Comment

by:ManikandanN
ID: 22686143
I managed to create like this, is this appropriate

CREATE PROCEDURE sp_guid
      @uuid    uniqueidentifier     -- guid
   
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
      set nocount on
      declare @uid        uniqueidentifier
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
select @Uid = newid()
END
GO
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22686153
almost
CREATE PROCEDURE sp_guid

      @uuid    uniqueidentifier  OUTPUT    -- guid

   

as

    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --

      set nocount on

      declare @uid        uniqueidentifier

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

select @Uid = newid()

END

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now