troubleshooting Question

MS SQL AND PASSING A TABLE NAME TO A SPROC

Avatar of Overthere
Overthere asked on
Microsoft SQL Server
5 Comments1 Solution107 ViewsLast Modified:
I have 5 different tables in which I want to create a new record, pass a value to it and return the primary key of the newly inserted record. I can not see the sense in creating 5 sproc to do this when  they are all going to be doing the samething – it’s the name of the table that varies. Makes sense to me to have one sproc to passthe table name to and return the primary key. I am trying to create a sproc in which I pass the table name programmatically to the sproc and return its primary key.  I am struggling with the construction of the sproc and how to code the call to it in my program. Can someone show me the way?
Here’s what I have for the sproc.
SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 GO
 -- ===================================================
 -- Author:            DA
 -- Description:      INSERT NEW RECORD WITH RESERVATION ID
                              AND RETURN PK OF NEWLY CREATED RECORD
 -- ====================================================
 CREATE PROCEDURE spNewGenricRecord
       @tablename nvarchar(100) 
 AS
 SET NOCOUNT ON;

 DECLARE @sql varchar(8000);


 SET @sql = ‘@ReservationId INT,@RECID INT OUTPUT AS 
                      INSERT INTO [' + PARSENAME(@tablename, 1) + '] (ReservationId)
                     Values(@ReservationId) Select @RECID = SCOPE_IDENTITY()                                 
                      RETURN @RECID';

 BEGIN TRY
 EXEC(@sql)
 END TRY
]
 BEGIN CATCH
    ‘RETURN SQL ERROR IF FAILS
END CATCH

 GO --end of proc

And here is the coding:

set rsM = Server.CreateObject("ADODB.Connection")
		rsM.Open vdbase
		rsM.Errors.Clear
		set rsCmmds = Server.CreateObject("ADODB.Command")
		set rsCmmds.ActiveConnection = rsM
		rsCmmds.CommandText = " spNewGenricRecord”
		rsCmmds.CommandType = adcmdStoredProc	
		rsM.BeginTrans
		rsCmmds.Parameters.Append rsCmmds.CreateParameter("RC",adInteger,adParamReturnValue)
		rsCmmds.Parameters.Append 
rsCmmds.CreateParameter("TableName",adVarChar ,adParamInput,30,vtblname)
		rsCmmds.Parameters.Append rsCmmds.CreateParameter("ReservationId",adInteger,adParamInput,4,vresid)
		rsCmmds.Parameters.Append rsCmmds.CreateParameter("AgencyId",adInteger,adParamInput,4,vagencyid)
rsCmmds.CreateParameter("RecId",adInteger,4,adParamOutput)
		rsCmmds.Execute
		if rsM.Errors.Count = 0 then
			rsM.CommitTrans
			vrecid =  rsCmmds.Parameters.Item("RecId")
		else 
			vrecid = 0 
			rsM.RollbackTrans
		end if
]
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros