SQL Server Updates

I need to change the Null values to the values inthe set field Is this correct?

Update tblItems
Set Manufacturerid = UnknownID, StatusID = 3, Pricetypeid = Guid, daterelease = GetDate()

Where optioncode is not null and
((ManufacturerID is Null) AND
(StatusID is Null) AND
(Pricetypeid is Null ) AND
(daterelease is Null))

 

is that scripted or is that a stored procedure?

Normally you have to declare your variables.
Create Stored Procedure "Procedure name"

@UnknownID int
AS

Update TBLItems
SET Manufacturerid=@UnknownID, StatusID=3,Pricetypeid=Guid,Daterelease=GETDATE()
WHERE .....

the only thing I would bring to your attention is the AND.. if there is ONE field that is not currently NULL then it will skipt the update. You may want to try OR instead, unless you're sure that if one field is empty then the rest are as well.

 

I'm doing this as a script so If it's done as a script then I don't have to declare my variable right?

 

you shoud always declare your variables. Depending on what scripting language you're using. It's good practice.

For VBScript or ASP using VBScript:

Dim UnknownID

update tblitems set Manufacturerid="&uknownid&",Statusid=3..

is your Guid a variable or are you using that in your table. I know that you can set a field to a GUID in SQL server. I've not much experience with that so that is why I ask.

 

The guid represent data from another table.
This is what I came up with: Is this the correct way?
CREATE PROCEDURE dbo.prutblItems

@OptionCode nvarchar (10), Null
@ManufacturerID varchar(255),
@StatusID varchar(255),
@PricetypeID varchar(255),
@Daterelease varchar(255),
@UserID nvarchar(40),
@ErrorMessage nvarchar(255) = NULL OUTPUT


AS

/*
** Object stored procedure: dbo.pruItems
**
** Called From: ?
**
** Description: ?
**
** Date Programmer Description
**-------------------------------------------------------------------------------------------------------------
** 03/09/04 Yvette Calhoun Changing Nulls fiedls w
**
*/


DECLARE
@iErr INT,
@iBeginTranCount INT

SELECT @iBeginTranCount = @@TRANCOUNT, @iErr = 0

--Transaction initiation
IF @iBeginTranCount > 0
BEGIN
SAVE TRANSACTION pruItems
END
ELSE
BEGIN
BEGIN TRANSACTION pruItems
END

IF (SELECT COUNT(*) FROM tblItems WHERE OptionCode <> @OptionCode) = 0
BEGIN
UPDATE tblItems
SET
OptionCode = @OptionCode,
ManufacturerID = @ManufacturerID,
StatusID = @StatusID,
PricetypeID = @PricetypeID,
Daterelease = @Daterelease,
UserID = @UserID,

WHERE OptionCode = @OptionCode

SELECT @iErr = @iErr + @@ERROR

UPDATE tblitems
SET Manufacturerid = UnknownID, StatusID = 3, Pricetypeid = Guid, daterelease = GetDate()
WHERE optioncode is not null and
((ManufacturerID is Null) or
(StatusID is Null) or
(Pricetypeid is Null ) or
(daterelease is Null))

SELECT @iErr = @iErr + @@ERROR

END
ELSE
BEGIN
SET @ErrorMessage = 'pruItems: OptionCode already exists with another group'
SET @iErr = @iErr + 1
END
/*Commit Or Rollback the transaction*/
IF @iErr <> 0
BEGIN
ROLLBACK TRANSACTION pruItems
IF @ErrorMessage Is NULL
SELECT @ErrorMessage = 'pruItems: Error Message'
ELSE
SELECT @ErrorMessage
RETURN @iErr
END
ELSE IF @iBeginTranCount > 0
BEGIN
SAVE TRANSACTION pruItems
RETURN 0
END
ELSE
BEGIN
COMMIT TRANSACTION pruItems
RETURN 0
END



GO

 

with transactions and everything if you need rollback, sure.

2 things though.
1. UnknownID : what is it, where is it coming from. Is a param being passed or just a text string.
2. If your ID variables are numbers (integers) then they should not be data typed as varchar(255) they should be int