Newbie: Using variables in select clause

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

SQL Server 2005 Forums

Transact-SQL (2005)

Newbie: Using variables in select clause

Author

Topic

mad marcus
Starting Member

12 Posts

Posted - 2007-09-14 : 03:41:22

Hi,

I am trying to get a stored procedure working and want to select a column based on a variable something like this:

ALTER PROCEDURE dbo.StoredProcedure1
(
@mycol varchar(80),
@productid int
)
AS
SET NOCOUNT ON

Select @mycol
From products
Where productid = @productid

RETURN

The @mycol variable is a value that is determined at runtime. I can execute the stored procedure OK but it doesn't return a value from the column held by @mycol although it does if I hard code a column name.

I am sure there is probably a simple answer to this but I am missing it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 03:47:27

You are walking on tha dark side of sql...

ALTER PROCEDURE dbo.StoredProcedure1 
(
@mycol varchar(80),
@productid int
)
AS
SET NOCOUNT ON 

declare @sql varchar(max)

set @sql= 'Select ' + @mycol + '
From products
Where productid = ' + convert(varchar, @productid)

exec(@sql)

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

mad marcus
Starting Member

12 Posts

Posted - 2007-09-14 : 04:05:05

Thanks Peso but when I try to run that I get an error stating that I must declare the scalar variable @productid

mad marcus
Starting Member

12 Posts

Posted - 2007-09-14 : 04:33:31

Thanks heaps now I get to go home after all

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:02:24

quote:
Originally posted by mad marcus

Thanks Peso but when I try to run that I get an error stating that I must declare the scalar variable @productid

You should avoid passing object names as parameter. Make sure you read Sommarskog's article fully

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources