Dynamic query

Hello

I have a problem with writing a query.Let me give an example:

Table:

ColA , ColB , ColC , Col1 , Col2 , Col3 , Col4 , Col5

Ok.I must write a SP and it gets a parameter,say @.param. if @.param=1 then in the select statement I will select Col1,if @.param=2 then I will select Col2 and so on.

How can I do this?

Thanks.

take note of this. this will provide you all the info you need

use northwind
select * from syscolumns where id =(
select id from sysobjects where name='employees')

here's the Sp:

create proc dynamicselect(
@.columnid int
)as
declare @.dynamicsql nvarchar(200)
select @.dynamicsql ='select '+ name+ ' from employees'
from
(
select * from syscolumns where id =(
select id from sysobjects where name='employees')
)as a

where colid=@.columnid
exec sp_executesql @.dynamicsql

go
execute dynamicselect 2
execute dynamicselect 1

|||

Joey gives an answer how. You could also do:

declare @.query varchar(max)
set @.query = 'select col ' + cast(@.param as int) + ' from table'

Or if the column names aren't actually numberd, use a CASE expression to pick the columns.

The question I have is why do this? If all columns are only useful one at a time, this is probably a design issue. If this is to support some user interface issue, the user interface ought to be redesigned.

I would also suggest that you might want to think about just having a procedure per column configuration that you end up with. This will be better all around for you, particularly in performance and security. It will take a bit more coding of course

|||

-

CREATE PROCEDURE dbo.dynsql

@.param int

AS

BEGIN

DECLARE @.q varchar(max)

select @.q = 'SELECT sym_no'+CAST(@.param AS nvarchar(2)) + ' FROM TABLE'

exec sp_executesql @.q

END

execute dynsql 2

--

I tried this but I get an error: "Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'.

What's wrong with this..?

|||Your variable @.q must be of type ntext, nchar, nvarchar to be used with procedure sp_executesql. Change your variable from the current varchar to nvarchar.|||D'oh! I forgot that :)