Some Stored Procedure Help Please

  • Thread starter Thread starter Vayse
  • Start date Start date
V

Vayse

Hi
I'd like to convert this access query into a stored prodedure.
INSERT INTO dbo_Clients_Schedule ( CS_ScheduleID, CS_Sequence, CS_Desc,
CS_Show, CS_ClientID )
SELECT dbo_ScheduleOfInsurances.ScheduleID,
dbo_ScheduleOfInsurances.Sch_Sequence, dbo_ScheduleOfInsurances.Sch_Desc,
True AS ShowSch, [Enter Client ID] AS SchClient
FROM dbo_ScheduleOfInsurances;

ClientID will be string of length 6.
I'll be then running this sp from code, but I think I'm ok on that part. Its
the actual writing of the stored procedure that has me confused.

Thanks
Vayse
 
When I see a names and parameters like « dbo_ScheduleOfInsurances » and «
[Enter Client ID] », I'm never sure if we are talking about an ADP project
(this newsgroup) or an MDB file with linked tables and SQL passthrough
queries.

Here's the squeleton of your SP:

CREATE PROCEDURE dbo.MyProcedure
(
@Enter_Client_ID varchar (6)
)
AS
SET NOCOUNT ON

INSERT INTO dbo_Clients_Schedule (CS_ScheduleID, CS_Sequence, CS_Desc,
CS_Show, CS_ClientID )

SELECT dbo.ScheduleOfInsurances.ScheduleID,
dbo.ScheduleOfInsurances.Sch_Sequence, dbo.ScheduleOfInsurances.Sch_Desc, 1
AS ShowSch, @Enter_Client_ID AS SchClient

FROM dbo.ScheduleOfInsurances

GO

Notice that I have replace the value of True with 1 as the constant True
doesn't exist in T-SQL and replace dbo_ with dbo.
 
Thanks for that Sylvian. You were right about the table names.
I'm working in an ADP, but I set up an append query in access so I could get
the syntax correct.

All works fine now.
This is the code I used

Dim cmdSSP As New ADODB.Command
Dim connSSP As ADODB.Connection
Dim parmClientID As ADODB.Parameter

On Error GoTo Err_CreateClientDefaults

With cmdSSP
.ActiveConnection = CurrentProject.Connection
.CommandText = "InitialClientNOF"
.CommandType = adCmdStoredProc
Set parmClientID = cmdSSP.CreateParameter
With parmClientID
.Name = "Enter_Client_ID"
.Type = adVarChar
.Direction = adParamInput
.Size = 7
.Value = Me.txtClientID
End With

.Parameters.Append parmClientID
.Execute
End With




Sylvain Lafontaine said:
When I see a names and parameters like « dbo_ScheduleOfInsurances » and
« [Enter Client ID] », I'm never sure if we are talking about an ADP
project (this newsgroup) or an MDB file with linked tables and SQL
passthrough queries.

Here's the squeleton of your SP:

CREATE PROCEDURE dbo.MyProcedure
(
@Enter_Client_ID varchar (6)
)
AS
SET NOCOUNT ON

INSERT INTO dbo_Clients_Schedule (CS_ScheduleID, CS_Sequence, CS_Desc,
CS_Show, CS_ClientID )

SELECT dbo.ScheduleOfInsurances.ScheduleID,
dbo.ScheduleOfInsurances.Sch_Sequence, dbo.ScheduleOfInsurances.Sch_Desc,
1 AS ShowSch, @Enter_Client_ID AS SchClient

FROM dbo.ScheduleOfInsurances

GO

Notice that I have replace the value of True with 1 as the constant True
doesn't exist in T-SQL and replace dbo_ with dbo.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Vayse said:
Hi
I'd like to convert this access query into a stored prodedure.
INSERT INTO dbo_Clients_Schedule ( CS_ScheduleID, CS_Sequence, CS_Desc,
CS_Show, CS_ClientID )
SELECT dbo_ScheduleOfInsurances.ScheduleID,
dbo_ScheduleOfInsurances.Sch_Sequence, dbo_ScheduleOfInsurances.Sch_Desc,
True AS ShowSch, [Enter Client ID] AS SchClient
FROM dbo_ScheduleOfInsurances;

ClientID will be string of length 6.
I'll be then running this sp from code, but I think I'm ok on that part.
Its the actual writing of the stored procedure that has me confused.

Thanks
Vayse
Click to expand...
Click to expand...
 
Back
Top