Error found creating stored procedure

Question

4.50/5 (2 votes)

See more:

SQL-Server-2005

C#

ASP.NET

, +

Hi,

Below mentioned error found while creating Stored procedure

The Code is....

SQL

CREATE PROCEDURE Allocate_call
    @comp_no as numeric(10) = select Complaint_no from Call_Reg,
    @Tech_name as varchar(50) = select Fname from New_Tech,
AS

    insert into Call_allocation values('@comp_no', '@Tech_name')

& the error is

Msg 156, Level 15, State 1, Procedure Allocate_call, Line 7
Incorrect syntax near the keyword 'select'.
Msg 1087, Level 15, State 2, Procedure Allocate_call, Line 8
Must declare the table variable "@Tech_name".
Msg 156, Level 15, State 1, Procedure Allocate_call, Line 9
Incorrect syntax near the keyword 'AS'.

Pls help..

Posted 3-Jan-13 22:03pm

Member 9693583

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kankeyan · Answer 1 · 2013-01-03T22:10:00

Solution 1

SQL

CREATE PROCEDURE Allocate_call
AS
BEGIN
   Declare @comp_no as numeric(10)
   Declare  @Tech_name as varchar(50)

   select @comp_no      =   Complaint_no from Call_Reg
   select @Tech_name    =   Fname from New_Tech

    insert into Call_allocation values(@comp_no, @Tech_name)
END

Posted 3-Jan-13 22:10pm

kankeyan

Comments

Suvabrata Roy 4-Jan-13 4:14am

Hi Friend,

In you solution @comp_no always set the last value from Call_Reg and same for @Tech_Name if don't mentioned order by clause the DataBase will order it by default depending on Index or DataType, so please be careful about that.

kankeyan 4-Jan-13 4:27am

Hi ,
I think he forgot to compare with Where clause in Select Statement and i think there must be a input param.

Member 9693583 4-Jan-13 4:47am

Thanks for ur suggestion. its working when we parse the stored procedure but its not working while executing query

abhay bansal · Answer 2 · 2013-01-03T22:16:00

Solution 3

- Remove comma from the second variable declared.
- Put braces as defined below.
- There might be case where more than one records are returning.
- Also, check what query needs to be exactly as both select will return n number of records. Use where condition to get exact value.
- PS: If you need the variables from db tables only. Then declare it as local variables instead of procedure parameters.

So use this and run and leme know if there is any error.

SQL

CREATE PROCEDURE Allocate_call
    @comp_no as numeric(10) = (select Top 1 Complaint_no from Call_Reg),
    @Tech_name as varchar(50) = (select Top 1 Fname from New_Tech)
AS

    insert into Call_allocation values('@comp_no', '@Tech_name')

Hope, this helps.

--
Thanks,
Abhay Bansal

Posted 3-Jan-13 22:16pm

abhay bansal

Updated 3-Jan-13 22:18pm

v2

Comments

Member 9693583 4-Jan-13 4:59am

hi, See my front end coding.... actually i want to select complaint no. from 1 dropdownlist(which comes from databse only) & Tech_name from another dropdownlist(which comes from database only) and insert the particular record of selected complaint no. in along with Tech_name in another table on button click

Code for ur reference
SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
private string s;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlComplaint.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillDropDownList();
ddlAllow.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillTechnicianDropDownList();
}
}

// Fill Dropdownlist
public void FillDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Complaint_no from Call_Reg where Status='Open'" , con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlComplaint.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

// Show data in GridView
protected void btnSearch_Click(object sender, EventArgs e)
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select * from Call_Reg where Complaint_no='" + ddlComplaint.SelectedItem.ToString() + "'", con);
dr = cmd.ExecuteReader();
GridView2.DataSource = dr;
GridView2.DataBind();
dr.Close();
con.Close();
}

// Fill Technician
public void FillTechnicianDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Fname from New_Tech", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlAllow.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

protected void btnAllo_Click(object sender, EventArgs e)
{
string var_comp_no;
string var_Tech_name;
var_comp_no = ddlComplaint.SelectedValue;
var_Tech_name=ddlAllow.SelectedValue;
SqlCommand cmd_insert = new SqlCommand();
SqlConnection con = new SqlConnection("SERVER=KSHITIJA-PC; Initial Catalog=CRM;Integrated Security=True");
cmd_insert.Connection = con;

cmd_insert.CommandType = CommandType.StoredProcedure;
cmd_insert.CommandText = "Allocate_Call";

cmd_insert.Parameters.Add(new SqlParameter("@comp_no", SqlDbType.Int));
cmd_insert.Parameters["@comp_no"].Value = var_comp_no; ;

cmd_insert.Parameters.Add(new SqlParameter("@Tech_name", SqlDbType.VarChar, 50));
cmd_insert.Parameters["@Tech_name"].Value = var_Tech_name;

try
{
con.Open();

int i = cmd_insert.ExecuteNonQuery(); ;
//int a = cmd1.ExecuteNonQuery();
if (i == 1)
{
ddlComplaint.SelectedItem.Text = "";
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Message: ", "Call Assigned to Technician", null, mb);
}

}
catch (Exception ex)
{
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Error: ", ex.Message, null, mb);

}
finally
{
con.Close();
}
}

& Code of stored procedure
CREATE PROCEDURE Allocate_call
AS
BEGIN
Declare @comp_no as numeric(10)
Declare @Tech_name as varchar(50)

select @comp_no = Complaint_no from Call_Reg

_Amy · Answer 3 · 2013-01-03T22:52:00

Try this:

SQL

CREATE PROCEDURE Allocate_call
    @comp_no numeric(10) = NULL,
    @Tech_name varchar(50) = NULL
AS
BEGIN
    --Check for NULL
    SELECT @comp_no = CASE WHEN @comp_no IS NULL THEN (select Complaint_no from Call_Reg) ELSE @comp_no END
    --Check for NULL
    SELECT @Tech_name = CASE WHEN @Tech_name IS NULL THEN (select Fname from New_Tech) ELSE @Tech_name END
    --Insert values
    insert into Call_allocation values(@comp_no, @Tech_name)
END

--Amit

madhuri@mumbai · Answer 4 · 2013-01-03T23:41:00

Solution 5

Try this,

SQL

CREATE PROCEDURE Allocate_call(@comp_no int,@Tech_name varchar(50))
AS
BEGIN
    select @comp_no = Complaint_no from Call_Reg;
    select @Tech_name = Fname from New_Tech;
    insert into Call_allocation values('@comp_no', '@Tech_name');
 END
GO

this will help you

Posted 3-Jan-13 23:41pm

madhuri@mumbai

Comments

Member 9693583 4-Jan-13 6:00am

Thanks madhuri... its working while running stored procedure but query giving me below error.. pls help

Query code

USE CRM;
GO
EXECUTE Allocate_call @comp_no, @Tech_name;

& error is
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@comp_no".

madhuri@mumbai 4-Jan-13 6:07am

try below,

USE CRM;
GO
EXECUTE Allocate_call '','';

passed here empty value becoz you are set value in SP.

*Suvabrata Roy* · Answer 5 · 2013-01-03T22:11:00

Solution 2

Hi,

I think default value should be a constant

SQL

CREATE PROCEDURE Allocate_call
    @comp_no as numeric(10) = 10,
    @Tech_name as varchar(50) = "TechSuva",
AS

    insert into Call_allocation values('@comp_no', '@Tech_name')

Posted 3-Jan-13 22:11pm

Suvabrata Roy

Comments

Member 9693583 4-Jan-13 4:45am

Hi,

But I want Tech_name & Comp_no should retrieve from database... pls suggest.. I want data of dropdownlist(which is retrived from database) to be insered in another table
Kindly guide

Suvabrata Roy 4-Jan-13 4:59am

So the drop down in UI, set value from cs page and run the procedure.

Member 9693583 4-Jan-13 5:09am

Yes I want to do this only bt having problem in procedure... error occured that procedure Allocation_call not found.. even though i was created it.

See my front end coding.... actually i want to select complaint no. from 1 dropdownlist(which comes from databse only) & Tech_name from another dropdownlist(which comes from database only) and insert the particular record of selected complaint no. in along with Tech_name in another table on button click

Code for ur reference

SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
private string s;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlComplaint.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillDropDownList();
ddlAllow.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillTechnicianDropDownList();
}
}

// Fill Dropdownlist
public void FillDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Complaint_no from Call_Reg where Status='Open'" , con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlComplaint.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

// Show data in GridView
protected void btnSearch_Click(object sender, EventArgs e)
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select * from Call_Reg where Complaint_no='" + ddlComplaint.SelectedItem.ToString() + "'", con);
dr = cmd.ExecuteReader();
GridView2.DataSource = dr;
GridView2.DataBind();
dr.Close();
con.Close();
}

// Fill Technician
public void FillTechnicianDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Fname from New_Tech", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlAllow.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

protected void btnAllo_Click(object sender, EventArgs e)
{
string var_comp_no;
string var_Tech_name;
var_comp_no = ddlComplaint.SelectedValue;
var_Tech_name=ddlAllow.SelectedValue;
SqlCommand cmd_insert = new SqlCommand();
SqlConnection con = new SqlConnection("SERVER=KSHITIJA-PC; Initial Catalog=CRM;Integrated Security=True");
cmd_insert.Connection = con;

cmd_insert.CommandType = CommandType.StoredProcedure;
cmd_insert.CommandText = "Allocate_Call";

cmd_insert.Parameters.Add(new SqlParameter("@comp_no", SqlDbType.Int));
cmd_insert.Parameters["@comp_no"].Value = var_comp_no; ;

cmd_insert.Parameters.Add(new SqlParameter("@Tech_name", SqlDbType.VarChar, 50));
cmd_insert.Parameters["@Tech_name"].Value = var_Tech_name;

try
{
con.Open();

int i = cmd_insert.ExecuteNonQuery(); ;
//int a = cmd1.ExecuteNonQuery();
if (i == 1)
{
ddlComplaint.SelectedItem.Text = "";
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Message: ", "Call Assigned to Technician", null, mb);
}

}
catch (Exception ex)
{
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Error: ", ex.Message, null, mb);

}
finally
{
con.Close();
}
}

& Code of stored procedure
CREATE PROCEDURE Alloc

Suvabrata Roy 4-Jan-13 5:58am

Add dbo or the schema Name before store procedure name, and try again
Apart from that did not found any error in your code

Error found creating stored procedure

5 solutions

Solution 1

Solution 3

Solution 4

Solution 5

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us