Solved

GUID generation in Stored Prcoedure

Posted on 2008-10-10
9
643 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using CTE to insert records into a table 2 33
Anyway to make these 2 SQL statements into one? 13 38
Sql Query 4 33
SQL query with cast 38 42
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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