[SOLVED] cfprocparam throwing Error Executing Database Query

cfprocparam throwing Error Executing Database Query

This error occurs when I run this code

Procedure studentinsert has no parameters and arguments were supplied.

<cfstoredproc procedure="studentinsert" datasource="student" result="res">
  <cfprocparam cfsqltype="cf_sql_varchar" value="john">
  <cfprocparam cfsqltype="cf_sql_varchar" value="cse">
</cfstoredproc>

mssql created table

CREATE TABLE student(
    studentid int identity(1,1)  PRIMARY KEY,
    studentname varchar(255),
    dept varchar(255),
    );

created procedure

CREATE PROCEDURE studentsub
AS
BEGIN
    SELECT *FROM student
 END;

exec studentsub

CREATE PROCEDURE studentinsert
AS
BEGIN
    INSERT INTO student (studentname,dept)
        VALUES ('john','cse');
END

Solution

The error is pretty clear - you need to define the parameters in the stored procedure, eg:

CREATE PROCEDURE studentinsert @studentname varchar(255), @dept varchar(255)
AS
BEGIN
    INSERT INTO student (studentname,dept)
        VALUES (@studentname, @dept);
END

Note that this example uses the same column types as the table definition in your question, however in most applications I would recommend nvarchar over varchar unless you are certain you will never need to support names with unicode characters not supported by varchar.