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

Microsoft SQL Server

Avatar of undefined
Last Comment
Overthere

8/22/2022 - Mon
Jim Horn

>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

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
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Overthere

ASKER
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!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Overthere

ASKER
thank you for helping :}