Link to home
Start Free TrialLog in
Avatar of Overthere
Overthere

asked on

MS SQL AND PASSING A TABLE NAME TO A SPROC

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

Open in new window


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
]

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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.
An example of why you would want to do this is if security will be different for the five tables, and it's easier to assign rights to different objects then conditional rights on a single object.

>SET @sql = ‘@ReservationId INT,@RECID INT OUTPUT AS
Since @ReservationId is defined as an input parameter, you're missing either defining it as a parameter or elsewhere in the SP.

If the five table names are known in advance, one way would be IF blocks, like this:
CREATE PROC spNewGenricRecord(@tablename nvarchar(100)) 
AS

Declare @id int

IF @tablename = 'burgerjoints'
   begin
   INSERT INTO burgerjoints VALUES whatever'
   SET @id = SCOPE_IDENTITY() 
   end

IF @tablename = 'whorehouses'
   begin
   INSERT INTO whorehouses VALUES whatever'
   SET @id = SCOPE_IDENTITY() 
   end

IF @tablename = 'icecreampalaces'
   begin
   INSERT INTO icecreampalaces VALUES whatever'
   SET @id = SCOPE_IDENTITY() 
   end

-- Return value
SELECT @id
GO

Open in new window

Avatar of Overthere
Overthere

ASKER

Below is the stored procedure that I created. It creates it without any errors. HOWEVER, when I execute the sproc within MS SQL console, it does not insert a record into any of the tables and therefore, does not return a RECID. What am I doing wrong??

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[ins_generic_travel]    Script Date: 3/25/2017 7:15:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ins_generic_travel]
 
        @tablename nvarchar(10),
        @ReservationId Int,    
        @RECID INT OUTPUT AS
BEGIN

IF @tablename = 'TravelEast'
     INSERT INTO TravelEast(ReservationId) Values(@ReservationId)              
     SELECT @RECID = SCOPE_IDENTITY()
	
IF @tablename = 'TravelWest'
     INSERT INTO TravelWest(ReservationId) Values(@ReservationId)              
     SELECT @RECID = SCOPE_IDENTITY()
    
IF @tablename = 'TravelNorth'
     INSERT INTO TravelNorth(ReservationId) Values(@ReservationId)              
     SELECT @RECID = SCOPE_IDENTITY()

IF @tablename = 'TravelOverSeas'
     INSERT INTO TravelOverSeas(ReservationId) Values(@ReservationId)              
      SELECT @RECID = SCOPE_IDENTITY()
	
RETURN @RECID
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am forgetting my basics...sigh. I add the begin/end (how could I have forgotten - LOL) I changed the nvarchar(10) to varchar(20).
Then I decided to look at the reservation table and the relationship that exists between them -  and sure enough the 5 tables have fk to it.
So once I made those changes and entered an exsting pk from the reservation table -
VIOLA! It inserted my record just fine. Thank you!
thank you for helping :}