Creating a trigger using a cursor

Hi all,
I need to create a trigger on all tables in a database that will insert into
an audit table username, and event on the table. I can create the trigger
individually, but I would like to put this into a cursor so I do not have to
run the trigger 500 times.
I am grabbing all user tables and trying to exec a string within the cursor
to create the triggers. I keep gettin eror by kyword insert. which I believe
is near
" INSERT INTO #inputbuffer"
Below is the code I am using:
TIA,
Joe
declare @.name varchar(100), @.str varchar(8000)
declare crscall cursor for
select name from sysobjects
where type = 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status = 0
begin
declare @.str varchar(8000),@.name varchar(50)
set @.name = 'testrights'
select @.str = 'IF EXISTS (SELECT name FROM sysobjects
WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
'FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll
deallocate crsCAllAre you sure you really want this kind of automation?
Anyway, change the script to print out the query strings instead of just
executing them. Then test them: parse them and attempt to execute them.
And when it's done - I don't want to scare you - you'll still have to test
them 500 times.
ML|||Thank you daniel,
I guess it was just an extra pair of eyes!
The first typo did the trick.
Thanks again.
Joe|||Thank you! this was helpful as well as Daniels.|||This is a one-time thing to create all the triggers so it won't get into
production code.
So good for the poster if he can automate the creation of the triggers.
But I agree with out on the last part, he's still have to test them all.
Maybe he can automate that part too. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8C32588A-D032-40D3-B554-D6A64EB83F15@.microsoft.com...
> Are you sure you really want this kind of automation?
> Anyway, change the script to print out the query strings instead of just
> executing them. Then test them: parse them and attempt to execute them.
> And when it's done - I don't want to scare you - you'll still have to test
> them 500 times.
>
> ML|||If he puts his mind to it, someday his entire life might get automated. :)
He'll have automated himself out of existence.
ML|||That's what I am looking for. Automation is a wonderful thing!|||Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;)
ML|||jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700:

> Hi all,
> I need to create a trigger on all tables in a database that will insert
> into an audit table username, and event on the table. I can create the
> trigger individually, but I would like to put this into a cursor so I do
> not have to run the trigger 500 times.
> I am grabbing all user tables and trying to exec a string within the
> cursor to create the triggers. I keep gettin eror by kyword insert. which
> I believe is near
> " INSERT INTO #inputbuffer"
> Below is the code I am using:
Did you copy and paste that code? If so, there are 2 errors I spotted
straight away, both near the word INSERT. Comments inline, look for Typo #1
and Typo #2.
Dan

> TIA,
> Joe
> declare @.name varchar(100), @.str varchar(8000)
> declare crscall cursor for
> select name from sysobjects
> where type = 'u'
> open crscall
> fetch next from crscall
> into @.name
> while @.@.Fetch_Status = 0
> begin
> declare @.str varchar(8000),@.name varchar(50)
> set @.name = 'testrights'
> select @.str = 'IF EXISTS (SELECT name FROM sysobjects
> WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
> '+''''+'TR'+''''+')
> DROP TRIGGER Audit_InsUpd'
> exec (@.str)
> select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
> ON '+@.name+
> 'FOR INSERT, UPDATE AS
Typo #1. There's no space between ' and FOR, so you'd end up with invalid
syntax here as the table name will be concatenated into FOR and then the
INSERT keyword is invalid as there is no FOR.

> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
> ' INSERT INTO #inputbuffer
Typo #2. There's a ' missing at the start of this line, so this INSERT won't
be inside the string being assigned to @.str, it's going to be run in the
trigger creating code and #inputbuffer doesn't yet exist as a table.
However, I'm pretty sure the error is due to typo #1 otherwise you'd have
received an error about table #inputbuffer not existing, the compiler might
not be getting this far.

> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> insert into Tbl_MSDBAudit
> select SUSER_SNAME(),@.qry
> END'
> select @.str
> exec (@.str)
> fetch next from crscall
> into @.name
> end
> close crsCAll
> deallocate crsCAll
>|||Hi
Run this Code
declare @.str varchar(8000),@.name varchar(50)
declare crscall cursor for
select name from sysobjects
where type =3D 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status =3D 0
begin
--set @.name =3D 'testrights'
select @.str =3D 'IF EXISTS (SELECT name FROM sysobjects
WHERE name =3D '+''''+@.name+'_Audit_InsUpd'+'=AD'''+' AND type =3D
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str =3D 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
' FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr =3D '+''''+'DBCC INPUTBUFFER('+
STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry =3D (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll=20
deallocate crsCAll=20
With warm regards
Jatinder Singhsql

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit table
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> > Good Day All,
> >
> > I am trying to create a trigger on a table and this trigger must update
> >
> > an Audit table which reflects the column name (the changes apply to),
> > the old value and the new value.
> >
> >
> > I have tried running through a cursor to dynamically update the Audit
> > table with the individual fields but this does not work since when
> > selecting from the inserted or deleted table one can either select all
> > fields or certain fields but I find it difficult to select only values
> > for the field that is current on my cursor.
> >
> >
> > I really will appreciate your help.
> >
> >
> > Regards,
> > Phonzo.
> >

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:[vbcol=seagreen]
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit tabl
e
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
>[StartDate] [datetime] NULL ,
>[EndDate] [datetime] NULL ,
>[Quantity] [int] NULL ,
>[Bar_Code] [varchar] (10) NULL ,
>[Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))
|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
sql

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row an
d
updating the Comment field with the reasons why test failed where appropriat
e.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update commen
t
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row a
nd
>updating the Comment field with the reasons why test failed where appropria
te.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comme
nt
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
<
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment = CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
--
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via SQLMonster.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via SQLMonster.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment => CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Creating a cursor with a dynamic database name.

You will either know this or you won't. I want to do thisthe following two lines of TSQL in one dynamically but none of the Declare Cursor statements work (when I try to pass in the Database name using a parameter). How do i dynamicically create a cursor to a table using a dynamic database/catalog name?

DECLARE curTest1 CURSOR SELECT * FROM testDB1.dbo.MyTable
DECLARE curTest2 CURSOR SELECT * FROM testDB2.dbo.MyTable

I've tried the following

DECLARE @.CatalogName NVARCHAR(5)
DECLARE @.sqlStr NVARCHAR (4000)

SET @.CatalogName = 'TestDB'
SET @.sqlStr = 'SELECT * FROM ' + @.CatalogName + 'dbo.Mytable;'

DECLARE curTest CURSOR FOR SELECT * FROM @.CatalogName.dbo.MyTable -- Which obviously should not and does not work.
DECLARE curTest CURSOR FOR @.sqlSTR -- Which I thought would work but also does not work.

CLOSE curTest
DEALLOCATE curTest

My environment is SQL Server 2000 environment SP3the answer you are looking for is sp_executesql....

you can't use a parameter value in the way you are trying...

you would need to dynamicly create and execute your sql statement using sp_executesql.

there have been a few posts in the last few days that explain this to the nth degree.|||You can't declare with dynamic sql...

DECLARE @.declare varchar(2000)

SET @.declare = 'DECLARE @.x int'

sp_executesql(@.declare)

And why do you want to use a cursor?

Think of dynamic sql being "outside" the scope of the current thread...|||Actually you're both wrong. I figured it out.

Turns out you need to use the EXEC command to execute the string.|||Yes I am...and I wish I wasn't

Why would you want to do this?

You going to build the Fetches dynamically?

How about the Declarations of the variables...That I don't think you can do indynamic sql

but this (to my UTTER amazement)..wrks:

USE Northwind
GO

DECLARE @.cmd varchar(8000), @.ShippedDate datetime
SELECT @.cmd = 'DECLARE myCursor CURSOR FOR SELECT ShippedDate FROM Orders'
EXEC(@.Cmd)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.ShippedDate
SELECT @.ShippedDate
CLOSE myCursor
DEALLOCATE myCursor

Good luck...|||The short answer is consolidated reporting on Accounting systems.

Most modern accounting systems allow for multiple companies to be managed from one server. To accomodate this, a seperate database is created for each company but fortunately the structure of the tables does not change between companyies . As a result, to report consolidated figures for the entire organization you want to have catalog names passed in dynamically especially if your oganization contains many companies.|||Can you post the sproc?

I'd like to see if there's a non cursor way...|||Here is one of them.

CREATE PROCEDURE sp_Sales_Summary_Update_02_03

@.CatalogName NVARCHAR(5),
@.ItemNumber NVARCHAR(31),
@.Warehouse NVARCHAR(11),
@.PurchaseTableName NVARCHAR(8),
@.PurchaseLineTableName NVARCHAR(8)
AS

DECLARE @.FromTheYear INT
DECLARE @.FromTheWeek INT
DECLARE @.ToTheYear INT
DECLARE @.ToTheWeek INT
DECLARE @.QuantityOrdered NUMERIC(19,5)
DECLARE @.strCursorString NVARCHAR(4000)

SET @.strCursorString = ''
SET @.strCursorString = @.strCursorString + 'DECLARE curQuantityOrdered CURSOR FORWARD_ONLY FOR '
SET @.strCursorString = @.strCursorString + 'SELECT '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheWeek, '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheWeek, '
SET @.strCursorString = @.strCursorString + ' ''' + @.CatalogName + ''' AS CompanyID, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR AS ItemNumber, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE AS Warehouse, '

SET @.strCursorString = @.strCursorString + ' SUM(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER) AS QuantityOrdered '
SET @.strCursorString = @.strCursorString + 'FROM ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + ' LEFT OUTER JOIN ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + ' '
SET @.strCursorString = @.strCursorString + ' ON ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PONUMBER = ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.PONUMBER '
SET @.strCursorString = @.strCursorString + 'WHERE '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.POSTATUS <> 6) AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER <> 0) '
SET @.strCursorString = @.strCursorString + 'GROUP BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
SET @.strCursorString = @.strCursorString + 'HAVING '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR = ''' + @.ItemNumber + ''') AND '
SET @.strCursorString = @.strCursorString + ' (''' + @.CatalogName + ''' = ''' + @.CatalogName + ''') AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE = ''' + @.Warehouse + ''') '
SET @.strCursorString = @.strCursorString + 'ORDER BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
PRINT @.strCursorString
EXECUTE(@.strCursorString)

OPEN curQuantityOrdered

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE tblSalesSummary
SET QuantityOrdered = @.QuantityOrdered
FROM
tblSalesSummary
WHERE
(CompanyID = @.CatalogName) AND
(ItemNumber = @.ItemNumber) AND
(Warehouse = @.Warehouse) AND
CASE
WHEN TheWeek < 10 THEN
CAST(TheYear AS nvarchar(4)) + '0'+ CAST(TheWeek AS nvarchar(2))
ELSE
CAST(TheYear AS nvarchar(4)) + CAST(TheWeek AS nvarchar(2))
END
BETWEEN
CASE
WHEN @.FromTheWeek < 10 THEN
CAST(@.FromTheYear AS nvarchar(4)) + '0'+ CAST(@.FromTheWeek AS nvarchar(2))
ELSE
CAST(@.FromTheYear AS nvarchar(4)) + CAST(@.FromTheWeek AS nvarchar(2))
END
AND
CASE
WHEN @.ToTheWeek < 10 THEN
CAST(@.ToTheYear AS nvarchar(4)) + '0'+ CAST(@.ToTheWeek AS nvarchar(2))
ELSE
CAST(@.ToTheYear AS nvarchar(4)) + CAST(@.ToTheWeek AS nvarchar(2))
END

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
END
CLOSE curQuantityOrdered
DEALLOCATE curQuantityOrdered
GO|||It Looks like alot but is really 2 steps. The first BLOB of TSQL creates the cursor. The second BLOB of TSQL updates the destination table.|||WOW! Dude...how long does it take to run?|||This particular sp executes in a fraction of a second. However it is run multiple times and depending on the volume of data required for processing can add up to hours (inconjunction with the other sp's I have running).

Creating a Cursor out of results of a SP

Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @.table_name = 'tablename', @.column_name='columnname'
--I want the type_name valueYou can use the INFORMATION_SCHEMA.COLUMN view...

What data do you want?|||I will look at that.

I was able to create a temp table, when i figured out the right syntax, and then selected the appropriate colomn out of the temp table.|||Good for you...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable' AND COLUMN_NAME = 'yourColumn'

createing new items based off a select?

I know this has to be possible with out using a cursor to loop through
this..
say I have tables like this...
Table A
==========
ItemID INT
Item TEXT
Table B
==========
PersonID int
ItemID int (from table A)
Table C
============
PersonID
Item
ItemID
Description
I want to do a select on table A get all items with the Item ID the person
in Table B hase and insert the result into Table C.
So if I have 2 items in A, and my Info in B, I want to do a query and have
records for each of them inserted into C with their info where it matches
together... I could easily do this with a cursor by looping through table A
looking for the ItemID of the current person then doing an Insert into table
C with the persons item information and the persons info... is there a way
to do this WITOUT a cursor and just a query? thanks!You're thinking in procedural language terms.
In T-SQL, it would go something like this...
insert into tablec (personid, item, itemid, description)
select b.personid, a.item, a.itemid, null
from tablea a
join tableb b on (a.itemid = b.itemid)
No idea where description is coming from so I nulled it.
"Brian Henry" <nospam@.nospam.com> wrote in message
news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into
table
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. If you had followed minimal netiquette, would your
pseudo-code look like this?
CREATE TABLE Items
(item _id INTEGER NOT NULL PRIMARY KEY,
Item_descrp VARCHAR(100) NOT NULL);
An item is not an attribute of a person; it is an entity, so we need to
fix your design.
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
. );
CREATE TABLE Purchases
(person_id INTEGER NOT NULL
REFERENCES People(person_id),
item_id INTEGER NOT NULL
REFERENCES Items(item_id),
PRIMARY KEY (person_id, item_id));
and have records [sic] for each of them inserted into C with their info
where it matches together... I could easily do this with a cursor by
looping through table A looking for the ItemID of the current person
then doing an Insert into table C with the persons item information and
the persons info... is there a way to do this WITOUT a cursor and just
a query <<
Your tables are not normalized. Rows are not records; no wonder you
think of procedural code and cursors instead of a query. Do not
materialize a new table, as if you were allocating a scratch tape in a
file system.
CREATE VIEW PurchaseReport (..)
AS
SELECT P.*, B.*
FROM Purchases AS P, People AS B, Items AS I
WHERE I.item_id = P.item_id
AND B.person_id = P.person_id;
The VIEW will always be current, unlike a new, redundant base table.|||Try,
insert into tablec (personid, item, itemid)
select b.personid, a.item, a.itemid
from tableb as b inner join tablea as a
on b.itemid = a.itemid
AMB
"Brian Henry" wrote:

> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into tab
le
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>
>|||thats what I was trying to remember right there.. thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:uGYGQc%23WFHA.2420@.TK2MSFTNGP12.phx.gbl...
> You're thinking in procedural language terms.
> In T-SQL, it would go something like this...
> insert into tablec (personid, item, itemid, description)
> select b.personid, a.item, a.itemid, null
> from tablea a
> join tableb b on (a.itemid = b.itemid)
> No idea where description is coming from so I nulled it.
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> A
> table
>

Create view from cursor

I have multiple locations that I want to create views for each
individual location.

I am using a cursor to create the views for each location. So, the
cursor grabs site #1 then <should> create view_site_#1, then grab site
#2 and <should> create view_site_#2.

For some reason it doesn't like the view name with the @.site in it.
Any ideas of how to get this done?

Here's the cursor...

declare @.site varchar(5)

declare c_site cursor for
select station from VHAISLCAUDIA.VISN_SITE
order by station

open c_site
fetch from c_site
into @.site

while (@.@.fetch_status = 0)
begin

CREATE VIEW Site_All_Data_+ @.site
AS
SELECT *
FROM dbo.[600_All_Suggested_Data]
WHERE (Site = @.site)

Print 'View for ' + @.site + ' Created'

fetch next from c_site into @.site
end
close c_site
deallocate c_site
return

endThis is actually one of the few times that a cursor and dynamic SQL can
be useful; this administrative scripting is a great target for this
sort of stuff.

Anyway, you need to use dynamic SQL for this:

DECLARE @.tSite TABLE (site varchar(5))
INSERT INTO @.tSite
SELECT 'ABCDE'
UNION ALL
SELECT 'FGHIJ'

declare @.site varchar(5)
DECLARE @.SQL nvarchar(2000)

declare c_site cursor for
select site from @.tsite

open c_site
fetch from c_site
into @.site

while (@.@.fetch_status = 0)
begin

SET @.SQL = 'CREATE VIEW Site_All_Data_' + @.site + '
AS
SELECT *
FROM dbo.[600_All_Suggested_Data]
WHERE Site = ''' + @.site + ''''

exec (@.SQL)

Print 'View for ' + @.site + ' Created'

fetch next from c_site into @.site
end
close c_site
deallocate c_site

HTH,
Stu|||Worked like a charm!

Thanks for helping a developer that forgets the 'simple' stuff
sometimes.

db55