Solved

GUID generation in Stored Prcoedure

Posted on 2008-10-10
9
641 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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