SPROC - How to use a parameter in a select stmt

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 2000 Forums

Transact-SQL (2000)

SPROC - How to use a parameter in a select stmt

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-11-12 : 14:14:11

I'm new to stored procedures, and I don't understand how to pass in a parameter that will control which column will be selected in a select statement. This code doesn't work, can someone tell me why ?

-- declare the procedure
create proc SelectAColumn @ColumnToSelect varchar(20)
as
select @ColumnToSelect
from MyTable

-- declare the variable to hold the column name I want to see
declare @ColumnToSelect varchar(20)
-- trying to select the column 'name'
set @ColumnToSelect = 'name'
-- execute the sproc
exec SelectAColumn @ColumnToSelect

What I get back is a computed column, all records containing 'name'.

Thanks,
Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-12 : 14:17:29

What you are trying to do is called dynamic sql. It is not recommended that you use dynamic sql unless there isn't a workaround. If you don't have a workaround (which I'm sure we can figure one out for you), then read up on dynamic sql by searching the forums. It is discussed a couple of times per week.

Dynamic sql is not recommended due to poor performance and bad security.

Tara

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-11-12 : 14:47:01

Thanks Tara,

I'll read up on Dynamic SQL.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-12 : 14:58:39

WHY do people insist on doing this? what is the point of this stord procedure? if you are not going to provide any abstraction or security in a stored procedure, but rather just build dyanmic SELECT statements blindly passing in parameters, why not just use SELECT statements and do away with stored procs all together?

i.e., why should this proc exits:

GetData 'somecolumn','sometable','somecondition'

when you can just say:

SELECT somecolumn FROM sometable WHERE somecondition

why even bother with a stored produre at all if all it is going to do is generate the SELECT statement dynamically?

(sorry, ranting a little. but the point of stored procs is to NOT do this.)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources