how to pass value to bind variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to pass value to bind variable

Author

Topic

BCJ
Starting Member

42 Posts

Posted - 2008-06-19 : 09:01:14

select field1 from table1 where field1 = @p_field1

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@p_field1".

Hi, i'm getting this error, could anybody please explain, how the syntax should be.........thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-19 : 09:03:31

you require to declare the variable with the name @p_field1

Chirag

http://www.chirikworld.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-19 : 09:04:08

I think its pretty obvious what the error and what corrective action is needed from your end.

Declare @p_field1 <data_type>

set @p_field1 = <some_value>

select field1 from table1 where field1 = @p_field1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BCJ
Starting Member

42 Posts

Posted - 2008-06-19 : 09:22:07

Thanks to all , also would like to know , how can i pass the parameter values at run time instead of doing "set @p_field1 = <some_value>" . is it possible without creating a procedure .....

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-19 : 09:50:20

No you can not pass the parameters values at run time ... without using sps or udfs

Do you mean that you require to fetch the value from the some table ?

Select @p_Field = Column_name from yourtable

Chirag

http://www.chirikworld.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 10:19:53

quote:
Originally posted by BCJ

Thanks to all , also would like to know , how can i pass the parameter values at run time instead of doing "set @p_field1 = <some_value>" . is it possible without creating a procedure .....

DIdnt understand what you're asking. If you're question was whether you could use variables without declaring answer is no.

BCJ
Starting Member

42 Posts

Posted - 2008-06-19 : 12:12:25

Thanks , that's what i was looking for , but just confirming that after executing this statement "select field1 from table1 where field1 = @p_field1"

it should ask me to enter the value for p_field1,

so, i can enter as my wish.........is it possible ...thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:14:09

quote:
Originally posted by BCJ

Thanks , that's what i was looking for , but just confirming that after executing this statement "select field1 from table1 where field1 = @p_field1"

it should ask me to enter the value for p_field1,

so, i can enter as my wish.........is it possible ...thanks

Nope. Unfortunately you cant have SQL Server prompt for user input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:16:01

However you could create a procedure with the statement like this
CREATE PROC YourProc
@p_field1 datatype
AS
select field1 from table1 where field1 = @p_field1
GO

and then call this with value you like

YourProc <value1>
YourProc <value2>...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources