Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
 
Below mentioned error found while creating Stored procedure
 
The Code is....
 
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 23:03pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
Suvabrata Roy at 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 at 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 at 4-Jan-13 4:47am
   
Thanks for ur suggestion. its working when we parse the stored procedure but its not working while executing query
Rate this: bad
good
Please Sign up or sign in to vote.

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.
 

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
  Permalink  
v2
Comments
Member 9693583 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Try this:
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
  Permalink  
v3
Comments
Member 9693583 at 4-Jan-13 5:05am
   
is this case working when complaint_no & Tech_name selected from dropdownlist (Which is also comes from database only)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Try this,
 

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
  Permalink  
Comments
Member 9693583 at 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 Patil at 4-Jan-13 6:07am
   
try below,
 
USE CRM;
GO
EXECUTE Allocate_call '','';
 
passed here empty value becoz you are set value in SP.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
I think default value should be a constant
 
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')
  Permalink  
Comments
Member 9693583 at 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 at 4-Jan-13 4:59am
   
So the drop down in UI, set value from cs page and run the procedure.
Member 9693583 at 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 at 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

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

  Print Answers RSS
0 OriginalGriff 386
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 215
3 /\jmot 189
4 Praneet Nadkar 173
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100