Dynamically Selecting columns

Hi all,

I need a select statement. In that select statement's column_list, I dont need a column, in such a way that column can be among a list of the available columns in the table. That means suppose there are column names with names A,B,C,D,E,F. I need to write a select statement in such a way,

select column_Name from table_Name

In the above column_name could be any among A,B,C,D,E,F, that could be decided programmatically.

Please help me. Very Urgent.

Chandu

Hi,

Try this.

Code Snippet

DECLARE @.SQL nvarchar(500)

DECLARE @.column nvarchar(100)

DECLARE @.table nvarchar(100)

set @.column = '*'

set @.table = 'table'

SET @.SQL = 'SELECT ' + @.column + ' FROM ' + @.table

EXEC sp_executesql @.SQL

regards,

Janos

|||

Chandu,

Using dynamic SQL, as Janos demonstrated, is the way to accomplish your task.

However, there are significant security cautions about using dynamic SQL. This article by Erland (and a couple from Microsoft) will go into more detail about using dynamic SQL and how to protect against most of the hazards.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Dynamically changing reports at runtime

Hi - I'm trying to decide among reporting options without spending too much
time going down the wrong path, so I hope someone can answer my questions
about Reporting Services capabilities...
With Reporting Services, can I dynamically (at runtime via code):
1) add or remove subreports from a report?
2) make a subreport visible or hidden?
3) add or delete columns?
4) make columns visible or hidden?
5) add or delete tables?
6) make a table visible or hidden?
Is doing any of these things fairly straightforward?
Thanks so much in advance,
SherylYes, you can use an expression for the Hidden property of respective report
elements to dynamically show/hide them at runtime.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sheryl Landon" <shland@.comcast.net> wrote in message
news:OoDKy5$pEHA.3244@.tk2msftngp13.phx.gbl...
> Hi - I'm trying to decide among reporting options without spending too
much
> time going down the wrong path, so I hope someone can answer my questions
> about Reporting Services capabilities...
> With Reporting Services, can I dynamically (at runtime via code):
> 1) add or remove subreports from a report?
> 2) make a subreport visible or hidden?
> 3) add or delete columns?
> 4) make columns visible or hidden?
> 5) add or delete tables?
> 6) make a table visible or hidden?
> Is doing any of these things fairly straightforward?
> Thanks so much in advance,
> Sheryl
>

Dynamic SQL stops responding with no error message

On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls another
SP (sp_BuildPayments) which has among several lines of code one specific lin
e
that cancel the process with no error message. The line is a dynamic SQL tha
t
follows below:
execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
I've tried a lot of things with no sucess to find out the problem. Acctually
the code stop running in this line but @.@.error is 0. Does anyone know how to
solve this?
Thanks in advance.
Cheers.
JorgeJorge Luis Ribeiro wrote:
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
> another SP (sp_BuildPayments) which has among several lines of code
> one specific line that cancel the process with no error message. The
> line is a dynamic SQL that follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' +
> @.Account_Value + ' from [PRM].[dbo].[' + @.TableName +
> ']' + ' where datepart(month, Event_Date) = ' +
> @.Event_Month + ' and datepart(year, Event_Date) = ' +
> @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
> Acctually the code stop running in this line but @.@.error is 0. Does
> anyone know how to solve this?
> Thanks in advance.
> Cheers.
> Jorge
Do the users have UPDATE rights on the underlying table? The update is
going to require a table scan every time because you do not have and
SARGable clauses in the query. It's possible you are running into a
locking/blocking issue. You should be able to see the error value
outside the EXEC, unless there is a trigger on the table which is
resetting the value. Do you have a trigger on the table?
David Gugick
Imceda Software
www.imceda.com|||Hi
Print ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
Run the output in QA and see if it does the job.
"Jorge Luis Ribeiro" <Jorge Luis Ribeiro@.discussions.microsoft.com> wrote in
message news:B259EFD8-FECF-4652-844B-1AF42FE3F697@.microsoft.com...
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
another
> SP (sp_BuildPayments) which has among several lines of code one specific
line
> that cancel the process with no error message. The line is a dynamic SQL
that
> follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
> ' from [PRM].[dbo].[' + @.TableName + ']' +
> ' where datepart(month, Event_Date) = ' + @.Event_Month +
> ' and datepart(year, Event_Date) = ' + @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
Acctually
> the code stop running in this line but @.@.error is 0. Does anyone know how
to
> solve this?
> Thanks in advance.
> Cheers.
> Jorge|||Actually there is a trigger that calls the sp_CloseBusinessAccount that call
s
the other one.
I've tried to check the @.@.error in both procedures with no sucess.
As you mentioned the lock issue, this table @.TableName is a table that has
been created by the same procedure I intend to update.
"David Gugick" wrote:

> Jorge Luis Ribeiro wrote:
> Do the users have UPDATE rights on the underlying table? The update is
> going to require a table scan every time because you do not have and
> SARGable clauses in the query. It's possible you are running into a
> locking/blocking issue. You should be able to see the error value
> outside the EXEC, unless there is a trigger on the table which is
> resetting the value. Do you have a trigger on the table?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>