May 24, 2012 at 3:48 am
I'm having one of those days...
I want to create a StoredProc that runs an Insert query into a table.
My main data table contains details around appts for customers. ApptID, ApptDate, CustomerNo, Customer Details etc
We use this to send text messages out daily to remind of up coming appts. The customer has the ability to text back if cant make it.
I want to extract back the appts that are cancelled.
I need to create a StoredProc that Inserts ApptID, CustomerNo and the datetime now into another table.
This is my StoredProc, when it executes I get the error, StoredProc has no parameters and arguments were supplied.
Where have I gone wrong?
ALTER PROCEDURE dbo.RunInsertCanxAppt
AS
DECLARE @ApptID int
DECLARE @CustNumber varchar(15)
INSERT INTO dbo.ApptsCanx (CustNumber, ApptID, CanxDate)
VALUES ((SELECT CustID FROM dbo.Data WHERE CustID = @CustNumber), (SELECT ApptID FROM dbo.Data WHERE ApptID = @ApptID), GETDATE()
My execute statment
EXEC dbo.RunInsertCanxAppt 53954, 123456
May 24, 2012 at 3:58 am
jez.lisle (5/24/2012)
I'm having one of those days...I want to create a StoredProc that runs an Insert query into a table.
My main data table contains details around appts for customers. ApptID, ApptDate, CustomerNo, Customer Details etc
We use this to send text messages out daily to remind of up coming appts. The customer has the ability to text back if cant make it.
I want to extract back the appts that are cancelled.
I need to create a StoredProc that Inserts ApptID, CustomerNo and the datetime now into another table.
This is my StoredProc, when it executes I get the error, StoredProc has no parameters and arguments were supplied.
Where have I gone wrong?
ALTER PROCEDURE dbo.RunInsertCanxAppt
AS
DECLARE @ApptID int
DECLARE @CustNumber varchar(15)
INSERT INTO dbo.ApptsCanx (CustNumber, ApptID, CanxDate)
VALUES ((SELECT CustID FROM dbo.Data WHERE CustID = @CustNumber), (SELECT ApptID FROM dbo.Data WHERE ApptID = @ApptID), GETDATE()
My execute statment
EXEC dbo.RunInsertCanxAppt 53954, 123456
What you've done is declare local variables rather than parameters. Check the code to this:
ALTER PROCEDURE dbo.RunInsertCanxAppt
(@ApptID int,
@CustNumber varchar(15)
AS
INSERT INTO dbo.ApptsCanx (CustNumber, ApptID, CanxDate)
VALUES ((SELECT CustID FROM dbo.Data WHERE CustID = @CustNumber), (SELECT ApptID FROM dbo.Data WHERE ApptID = @ApptID), GETDATE()
That ought to get you where you need to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2012 at 4:04 am
Thats it 😀
Brilliant thanks for the help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply