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

OverthereAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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

0
OverthereAuthor Commented:
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

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
IF @tablename = 'TravelEast'
     INSERT INTO TravelEast(ReservationId) Values(@ReservationId)              
     SELECT @RECID = SCOPE_IDENTITY()

One of the quirks of T-SQL is that an IF block executes ONLY the next line if the condition is met, and you have two lines, so you'll have to surround them in a begin..end to execute as one batch

IF @tablename = 'TravelEast'
     begin
     INSERT INTO TravelEast(ReservationId) Values(@ReservationId)              
     SELECT @RECID = SCOPE_IDENTITY()
     end


>@tablename nvarchar(10)
>IF @tablename = 'TravelNorth'
You can't assign more then ten characters into an nvarchar(10), so the above IF will never evaluate to TRUE, so make the number bigger.  Also unless we're talking Unicode characters you can lose the n and define it as varchar.

>INSERT INTO TravelEast(ReservationId) Values(@ReservationId)    
Might not be a bad idea to add this line, execute, then copy-paste into SSMS and execute and see what error you get..
If the statement does not insert a row then there *should* be an error message.
PRINT 'INSERT INTO TravelEast(ReservationId) Values(' + @ReservationId +')'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OverthereAuthor Commented:
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!
0
OverthereAuthor Commented:
thank you for helping :}
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.