Need Assistance Creating a stored procedure

[Shawn Fletcher]

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at sh****@sccnet.com

Thanks,
Shawn

[Tyler Hudson]

Hi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.
Regards,

Tyler
"Shawn Fletcher" <sh****@sccnet.com> wrote in message
news:40***********************@newsreader.goldenga te.net...

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at sh****@sccnet.com

Thanks,
Shawn

[Shawn Fletcher]

Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn
"Tyler Hudson" <Ty****@Spam.MeNOTallpax.com> wrote in message
news:cd**********@news.datasync.com...

Hi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.
Regards,

Tyler
"Shawn Fletcher" <sh****@sccnet.com> wrote in message
news:40***********************@newsreader.goldenga te.net...

Hi,

I'm trying to work around a bug that our helpdesk software has. When a

new

issue is created, it cannot automatically default 2 fields to the value of No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10 minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very much experience with SQL statements.

Please email me at sh****@sccnet.com

Thanks,
Shawn

[Tyler Hudson]

It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)
"Shawn Fletcher" <sh****@sccnet.com> wrote in message
news:40***********************@newsreader.goldenga te.net...

Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing a Search and replace.

Thanks,
Shawn
"Tyler Hudson" <Ty****@Spam.MeNOTallpax.com> wrote in message
news:cd**********@news.datasync.com...

Hi Shawn,

A much better way would be to set up default values of "No" for those

columns and disallow nulls. You can do this through Enterprise manager or through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.
Regards,

Tyler
"Shawn Fletcher" <sh****@sccnet.com> wrote in message
news:40***********************@newsreader.goldenga te.net...

Hi,

I'm trying to work around a bug that our helpdesk software has. When

a new

issue is created, it cannot automatically default 2 fields to the

value of No like we need it to.

I have a field called "Audited" and one called "Billed to Client".

When

a new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10 minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very much experience with SQL statements.

Please email me at sh****@sccnet.com

Thanks,
Shawn

[Shervin Shapourian]

Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin
"Shawn Fletcher" <sh****@sccnet.com> wrote in message news:<40***********************@newsreader.goldeng ate.net>...

Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn
"Tyler Hudson" <Ty****@Spam.MeNOTallpax.com> wrote in message
news:cd**********@news.datasync.com...

Hi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.
Regards,

Tyler
"Shawn Fletcher" <sh****@sccnet.com> wrote in message
news:40***********************@newsreader.goldenga te.net...

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new issue is created, it cannot automatically default 2 fields to the value of No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10 minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very much experience with SQL statements.

Please email me at sh****@sccnet.com

Thanks,
Shawn