MS SQL AND PASSING A TABLE NAME TO A SPROC

Overthere
Overthere used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

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

Author

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

SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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 +')'

Author

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!

Author

Commented:
thank you for helping :}

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial