Stored procedure: "select @var"

[solo1234]

Dear all,

Using sql server 2005.

We are busy with a stored procedure.

Please look at the code:

declare @var varchar(10), @nr varchar(10)
set @var='field1'
set @nr = (SELECT @var FROM myTable)

In the table is only one record.

The result of Print @nr: 'field1'

Is it possible that the sp is acting the @var as a field? Can give someone the syntax?

Nice regards,
Michelle.

 

[gmmastros]

Why is there only one row in the table? More importantly, will there ever be more rows in this table?

Most of the time, when there is a single row in a table, the table is actually a configuration table. In which case, it would be better to have 2 columns and multiple rows, like this:

[tt]
Value Name
------ ----
Field1 X
Field2 7
Field3 True
[/tt]

Having your table structured like this makes things a lot easier.

The reason your query is not behaving the way you expect, is for a reason that you don't expect. SQL Server is viewing your @var as though it were a constant.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

The result you get is correct because you are selecting the literal value, not the column name inside the variable. In order to do what you want you need to look into Dynamic SQL. However, George's suggestion is the better route to take.