Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)

dynamic Instead of update trigger problem

Hello,

I am trying to use an instead of update trigger to alter a lastupdated field in the table when any of the other fields are modified by an update. I want to do this as I can't rely on the applications updating the date when they alter other data. The trigger looks something like this.

CREATE TRIGGER [noidentitytableinsteadupdate] ON [dbo].[noidentitytable]

INSTEAD OF UPDATE

AS

if(not update(updatedat))

begin

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = GetDate()

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

end

else

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = inserted.updatedat

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

The problem I am facing is when you add say another 10 fields to the table, and I want to ONLY do an update to the fields that have been updated by the original statement. In effect I want to check each column using update(column) and include it in the new update statement along with the lastupdate field. Dynamic sql does not have access to the inserted table so how would you do this?

The reason I want to exclude all the unmodified fields is because of existing after triggers that fire based on changes to individual column changes. I tried this in an after trigger originally but as this is done as an additional update this causes multiple fires of other triggers.

If the original update was

update noidentitytable set text = 'hello', state = 'updated',

but there were actually 10 columns in the table then the actual update I want to do is.

update noidentitytable set text = inserted.text, state = 'updated', updatedat = GetDate() from noidentitytable INNER JOIN inserted ON (noidentitytable.id = inserted.id)

Or perhaps what I really want to do is get hold of the original SQL and insert text change the datefield.

Can someone enlighted me please?

It would definitely be better to do this in an after trigger. Can't you just add a condition to all the other after triggers to do nothing if the updatedate column is the one being updated?|||

Why would you want to reissue the UPDATE statement again? It takes lot of work to perform an update and you will just make the entire UPDATE statement slower. It seems like you should just create a SP that performs the necessary UPDATE statement, add default on the updatedat column and use DEFAULT keyword in the UPDATE statement. This will be much more scalable, run more efficiently and easy to manage.

|||

I understand that you have to issue the update statement yourself in a instead of trigger, or am I missing a way of committing the original statement first?

I do use the default column value for the date, however setting the date to the default on the update is not much different than setting to GetDate().

what I really wanted was all the values that have changed from the inserted table plus some other default information ie date, user who modified.

|||

Perhaps I should give a different scenario that would require the same solution.

If I was using an instead of update trigger on a view and the underlying table structure changed but to remain backward compatible I left the view definition the same how would you go about ensuring only the fields that the client was updating were updated correctly in the underlying tables?

I would assume this would require some dynamic sql to check the updated columns and then update the tables underneath. Surely you would not update every column even if the value had not changed?

Dynamic If Update() in Trigger - Urgent!

Hi All

I have a question about generating dynmamicly If Update() statement in a
trigger..

in My db, there is a table that holds some column names of an another table.
for example;

Columns Table-A: Col1, Col2, Col3, Col4,Col5

Table-B: Col2, Col5 (The selected columns of Table A)

Then, in the Trigger of Table-A I use;

Select name from syscolumns where id=object_id('Table-A')

fetch next from TableA_Cursor into @.strColName

then, I used a statement like this..

if UPDATE(' + @.strColName + ')

But it gives "incorrect syntax" error..

How can I write this line?

Thanks alot in advance...

--
Message posted via http://www.sqlmonster.comI don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.

If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.

If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.

If you need more help, please post a fuller description of your problem
including DDL and sample data.

--
David Portas
SQL Server MVP
--

dynamic cube filter

Does anybody know how to put dynamically a filter into incremental update of a cube, which aborts the update if data is redundant.

I assume that time dimension in the cube needs to be checked ex. if the monthly data already exists in the cube, and it needs to be compared with a month from the view which underlies cubes fact table. However I don't know enough about MDX or cubes in general to figure this out :-)

Is it possible to delete , lets say a particular month from time dimension and all updates from this month, from a cube?

Not sure what your schenario is.

Few suggestions:

One take a look at the whitepaper talking about Analysis Services processing http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Second to answer your question about deleting data from dimension. The answer is yes, but only in case if you are deleting only few memebers. If you only going do delelte one customer from the dimension it should be fine. But not deleting all days in a month in Time dimension. Analysis Server actually not going to delete intenal record for dimension member, it just going to mark it as deleted. So in time you'd have to fully reprocess your dimension.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

dynamic cube filter

Does anybody know how to put dynamically a filter into incremental update of a cube, which aborts the update if data is redundant.

I assume that time dimension in the cube needs to be checked ex. if the monthly data already exists in the cube, and it needs to be compared with a month from the view which underlies cubes fact table. However I don't know enough about MDX or cubes in general to figure this out :-)

Is it possible to delete , lets say a particular month from time dimension and all updates from this month, from a cube?

Not sure what your schenario is.

Few suggestions:

One take a look at the whitepaper talking about Analysis Services processing http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Second to answer your question about deleting data from dimension. The answer is yes, but only in case if you are deleting only few memebers. If you only going do delelte one customer from the dimension it should be fine. But not deleting all days in a month in Time dimension. Analysis Server actually not going to delete intenal record for dimension member, it just going to mark it as deleted. So in time you'd have to fully reprocess your dimension.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic Column Names?

Using SQL 2000
I am passing in the name of the column I want to update, @.Column. When I
SELECT @.Column it returns the value of @.Column instead of the value of the
actule table column. I have tried to use
Col_Name(OBJECT_ID('antwerp_cutoff'),OBJ
ECT_ID(@.Column)) but this does not
get the column name.
When I pass in Display1 I need to get back the value 2/2/2005
|Display1 | Display2|
--
|2/2/2005 | 2/3/2005|
--
Any help would be greatly appreciated.
Thanks,
Scott R. Butler
Stored Proc:
Create Procedure "Update_Antwerp_Displays"
(
@.Column varchar(256)
)
As
Declare @.i int
Declare @.Count int
set @.Count = (SELECT Count (*) FROM antwerp_trans)
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
1 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 1
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
2 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 2
UPDATE antwerp_depart
Set @.Column = (select @.Column+(Select Delta from antwerp_depart where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
UPDATE antwerp_arrive
Set @.Column = (select @.Column+(Select Delta from antwerp_arrive where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
While (@.i < @.Count)
BEGIN
UPDATE antwerp_trans
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
@.i ) from antwerp_cutoff where Delta = 0)
WHERE ID = @.i
SET @.i = @.i +1
ENDYou cannot use variables for tables, columns, etc. in queries. You'll
need to use dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for the quick response... even though it was not the response I was
looking for.
Quess I will have to find another way to get it done.
Scott
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OYjcU7uDFHA.3324@.TK2MSFTNGP15.phx.gbl...
> You cannot use variables for tables, columns, etc. in queries. You'll need
> to use dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Go back to your freshman software engineering books and look up the
concepts of coupling and cohesion.
What you want is a procedure with so little cohesion that any random
user, present or future, has more control over the RDBMS than the DBA.
This is **foundations of programming**, not anything particular to SQL.|||It seems that you are using column names to represent data. This is a
fundamental mistake and causes lots of problems, such as the one you
are having here. Columns should represent a single attribute and
repeating groups of columns are in violation of First Normal Form. Fix
your table design and you won't need messy dynamic SQL to accomplish
this.
David Portas
SQL Server MVP
--

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
Rob
A couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
--
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
RobA couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad