stored procedure

Gurjeet Singh

13y2.4k.NET

sir,

I am taking value from textbox1 and trying to pass that value to stored procedure. but while creating stored procedure I am getting below error

Msg 102, Level 15, State 1, Procedure proc_1, Line 8

Incorrect syntax near '@name'.

in c#

string nam = " and city='" + textbox1.Text + "'";

cmd.Parameters.AddWithValue("@name", nam );

create procedure proc_1

(

@name varchar (max)

)

begin

select [country] from table_name where city='delhi' @name

end

please let me know, how can I insert the textbox1 value into @name variable

31 Replies

Know the answer? Post it — somebody with the same question will find it here.

Clive Scott13y ago
What is your SELECT statement supposed to do? that @name at the end of your SELECT could be the problem

select [country] from table_name where city='delhi' @name
0
Deepesh13y ago
First of all your query in the procedure is wrong.
When you run the create script you will get the incorrect syntax error because it expects some kind of condition to be applied to it.

try this instead
string nam = textbox1.Text;

cmd.Parameters.AddWithValue("@name", nam );

create procedure proc_1
(
@name varchar (max)
)
as
begin
select [country] from table_name where city = @name
end
0
sabdhi naik13y ago
create procedure proc_1
(
@name varchar (max)
)
as
begin
insert into table_name(name) values(@name)
end

This is the procedure for inserting-----
0
Dorababu Meka13y ago
Michale mark why you need and when you are unable to get any result?
0
Sudhakar Chaudhary13y ago
You Will not Get Any Result if you r use AND.
0
Gurjeet Singh13y ago
Dorababu M,

But i want to pass and city=' toronto'

select Country from tblCity where CityName='delhi' and city=' toronto'

is it possible.

Or let me know any alternative in stored procedure to validate data...
0
Sudhakar Chaudhary13y ago
create procedure proc_1
(
@name varchar (max)
)
as
begin
if(@name is null || '')
select [country] from table_name where city='delhi'
else
select [country] from table_name where city='delhi' OR city=@name
end
0
Santhosh Kumar Jayaraman13y ago
then try this
create procedure proc_1
(
@name varchar (max)
)
as
begin
If @name is not null and @name>''
select [country] from table_name where city='delhi' and city=@name
else
select [country] from table_name where city='delhi'
end

When passing from UI, make sure if user didnt enter then @name should be passed blank
0
Dorababu Meka13y ago
You will not get any result from this query so what is the use of this?
0
Gurjeet Singh13y ago
Dorababu,

create procedure [dbo].[proc_1](@name varchar(max))
as begin
select Country from tblCity where CityName='delhi''+@name+'
end

suppose

if(textbox1.text="")
{
string nam =" ";

}
else
{
string name= " and city='" + textbox1.Text + "'";
}

cmd.Parameters.AddWithValue("@name", nam );

if TextBox1.text is empty then store procedure will work like this

select Country from tblCity where CityName='delhi''

or if TextBox1.Text is having value then store procedure will work like this

select Country from tblCity where CityName='delhi' and city=' toronto'

0
Dorababu Meka13y ago
You didn't answer my question what you are trying to achieve firstly tell that

Try this

create procedure [dbo].[proc_1](@name varchar(max))
as begin
select Country from tblCity where CityName='delhi''+@name+'
end
0
Gurjeet Singh13y ago
yes , Sudhakar Chaudhary
0
Gurjeet Singh13y ago
yes, i m getting error... incorrect syntax
0
Dorababu Meka13y ago
Try this

create procedure [dbo].[proc_1](@name varchar(max))
as begin
select Country from tblCity where CityName='delhi''+@name+'
end

but i didn't find any use of this so let us know what you are trying to do
0
Sudhakar Chaudhary13y ago
I Think Michale Want To Pass 'AND' Condition With @name Parameter??
0
Dorababu Meka13y ago
There is no way of getting what you desired with the one you are trying to do, and as per you said you have to use AND operation this will not give you the result you need so tell what you are trying to achieve exactly
0
Dorababu Meka13y ago
Does your stored procedure get saved? I think you will have an error Incorrect syntax near '@name'.
0
chellappan pandiarajan13y ago
Hi

change you SQL Proc like below

create procedure proc_1
(
@name varchar (max)
)
as
begin
select [country] from table_name where city in ('delhi', @name)
end

and Change you C# coding like below
if (textbox1.text = "")
{
string name = " ";

}
else
{
string name = "'" + textbox1.Text + "'";
}

it will work.

-Pandian
0
Dorababu Meka13y ago
Also post the table data and what you would like to achieve from that table
0
Dorababu Meka13y ago
Can you tell what you are trying to achieve
0
Gurjeet Singh13y ago
suppose

if(textbox1.text="")
{
string nam =" ";

}
else
{
string name= " and city='" + textbox1.Text + "'";
}

I am using stored procedure for above purpose
0
Dorababu Meka13y ago
You said AND but you didn't have that in your stored procedure
0
Santhosh Kumar Jayaraman13y ago
what is the purpose of having it one after another?

what your really want to query?

you want to query something like
where city ='Delhi address1' like this??
where address1 is one of the value for @name.

If that is the case.,then try

create procedure proc_1
(
@name varchar (max)
)
as
begin
select [country] from table_name where city='delhi ' + @name
end
0
Gurjeet Singh13y ago
no,

i want to use only like this :

where city='delhi' @name

0
Gurjeet Singh13y ago
no,

i want to use only like this :

where city='delhi' @name

0
Dorababu Meka13y ago
Try this

SELECT country FROM table_name WHERE
city='Delhi' AND city=@name
0
Santhosh Kumar Jayaraman13y ago
ok still you face any problem?
0
Gurjeet Singh13y ago
boss,

I want to use only AND condition
0
Santhosh Kumar Jayaraman13y ago
ideally it should be either delhi or some other city right?

WHERE city='delhi' OR city=@name
0
Gurjeet Singh13y ago
i am passing

AND city='" + textbox1.Text + " into @name variable.

i know that WHERE condition must be like:

WHERE city='delhi' AND city=@name

0
Santhosh Kumar Jayaraman13y ago
why did you have the following condition ?
where city='delhi' @name

it is wrong. it should be
where city=@name
0