By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,883 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,883 IT Pros & Developers. It's quick & easy.

Need Assistance Creating a stored procedure

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jul 20 '05 #2

P: n/a
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


Jul 20 '05 #3

P: n/a
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



Jul 20 '05 #4

P: n/a
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


Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.