Solved

GUID generation in Stored Prcoedure

Posted on 2008-10-10
9
646 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 143

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22685842
can you please clarify the context? ie what exactly you are trying to achieve?
0
 

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 143

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 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