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.
And here is the coding:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
thank you for helping :}
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:
Open in new window