x

Table valued Parameters

Hi, Could anybody tell me how to use the Table valued parameters for this procedre, I have the paramters for this procedure placed in one of the table in the database. the database has 489 records How can i utilze the concept to feed the parameter to the stored proc?

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[customerregistration] ( @customerid VARCHAR(80), @Name VARCHAR(80), @aliasname VARCHAR(80), @startdate DATETIME, @enddate DATETIME, @DunsName VARCHAR(50), @DunsNumber VARCHAR(14)

--@gs_website varchar(100)

   )

AS BEGIN
DECLARE @uname VARCHAR(10)
DECLARE @errorNum INT, @errorMessage VARCHAR(200) ;
BEGIN TRY
BEGIN TRAN ; IF EXISTS ( SELECT * FROM cim.organisation WHERE MRID = @customerid ) BEGIN SET @errorMessage = 'Organisation id ' + @customerid + 'already exists' RAISERROR (@ErrorMessage,16,1) ;
END ; INSERT INTO [CIM].[Organisation] ( [ParentOrganisation], [aliasName], [description], [localName], [mRID], [name], [pathName], [ElectronicAddress], [phone1], [phone2], [postalAddress], [streetAddress], [businessRoleID], [gs_createdDateTime], [gs_startDate], [gs_endDate], [status], [ModelingAuthority], [gs_website] ) VALUES ( NULL, @AliasName, @name, NULL, @customerid, @name, NULL, 1, 1, 1, 2, 1, 1, 1, @startDate, @endDate, 1, 1, 'www.company.com' ) ;
PRINT 'inserted into Organization '
DECLARE @NewRecordId INT ;
SELECT @NewRecordId = SCOPE_IDENTITY() ;
INSERT INTO [CIM].[Customer] ( [organisationID], [pucNumber], [specialNeed], [vip], [PlannedOutage], [customerKind], [gs_DUNSname], [gs_DUNSnumber] ) VALUES ( @NewRecordId, NULL, NULL, NULL, NULL, NULL, @DunsName, @DunsNumber )
PRINT 'inserted into customer' INSERT INTO [CIM].[ActivityRecord] ( [createdDateTime], [category], [reason], [severity], [status], [gs_createdByUserId], [gs_data] ) VALUES ( GETDATE(), 'ORGANISATION_CREATED', 'customer load', NULL, '1', USER_ID(), NULL )

               PRINT 'inserted into activity record'
               DECLARE @NewRecordId2 INT ;
               SELECT    @NewRecordId2 = SCOPE_IDENTITY() ;    
               INSERT    INTO [GRIDSTAR].[JoinOrganisationToActivityRecord] ( [activityRecordID],
                                                           [organisationID] )
               VALUES    ( @NewRecordid2, @newrecordid )    
               PRINT 'inserted into join organisation to activity Record'

               COMMIT TRAN ;
         END TRY

         BEGIN CATCH
               IF @@trancount > 0 
                  ROLLBACK TRAN ;
               SELECT    @errorNum = ERROR_NUMBER(),
                         @ErrorMessage = ERROR_MESSAGE() ;
         END CATCH ;
         IF @ErrorMessage IS NOT NULL 
            RAISERROR(@ErrorMessage, 16, 1) ;

   END ;

more ▼

asked Feb 27, 2011 at 10:19 PM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You could create a data type to accept the data described by the variables you have declared for the sproc and perform the inserts using that.

 CREATE TYPE CustReg 
 AS TABLE 
 (
 customerid varchar(80),
 Name varchar(80),
 aliasname varchar(80),
 startdate datetime,
 enddate datetime,
 DunsName varchar(50),
 DunsNumber varchar(14)
 )

As you are moving from one table insert to the next and table type variables are read only, you will need to take a look at maybe storing that information in a local table variable/temp table to allow further manipulation.

You are using SCOPE_IDENTITY() to pass the newly created IDs around. You could change that use the OUTPUT option and return the IDs that are created using the table variable.

 INSERT    INTO [CIM].[Organisation] .....
 SELECT ...
 FROM @CustReg
 OUTPUT -- <--- This outputs the result of the insert
        --  you can also OUTPUT INTO a different table/temp table etc.
 inserted.id,
 inserted.some_other_column_that_you_need
more ▼

answered Feb 28, 2011 at 01:40 AM

avatar image

WilliamD
26.2k 18 35 48

I just would like to add a bit to William's answer. He mentions the table valued parameters are read only, and thus, they must be declared as such, readonly, i.e.

create proc dbo.customerregistration 
(
    @table CustReg readonly
)
as -- etc

I have a more or less complete code sample in the answer to this question. It also includes a C# snippet showing how to populate and pass the table valued parameter to the procedure from the front end.

Feb 28, 2011 at 08:10 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x780
x476
x19

asked: Feb 27, 2011 at 10:19 PM

Seen: 1541 times

Last Updated: Feb 27, 2011 at 10:54 PM

Copyright 2017 Redgate Software. Privacy Policy