SQL help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL help

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-21 : 10:51:23

Hi all, hope you can help,

I have a problem with an SQL command, and hope you can help me out.

Kind new to SQL so finding the answer difficult to find, but I do think it's to do with an UPDATE statement.

I have a function that executes a stored procedure (spUpdateStoreDetails) that updates a table, but when i run the procedure only one of the two columns gets updated (fk_windowID). I need both the fk_window and fk_floorplans to get updated

here is the query

CREATE PROCEDURE [dbo].[spUpdateStoreDetails]
@iStoreID DECIMAL
,@sOpensMonday VARCHAR(8)
,@sClosesMonday VARCHAR(8)
,@sOpensTuesday VARCHAR(8)
,@sClosesTuesday VARCHAR(8)
,@sOpensWednesday VARCHAR(8)
,@sClosesWednesday VARCHAR(8)
,@sOpensThursday VARCHAR(8)
,@sClosesThursday VARCHAR(8)
,@sOpensFriday VARCHAR(8)
,@sClosesFriday VARCHAR(8)
,@sOpensSaturday VARCHAR(8)
,@sClosesSaturday VARCHAR(8)
,@sOpensSunday VARCHAR(8)
,@sClosesSunday VARCHAR(8)
,@sTelNumber VARCHAR(50)
,@sFaxNumber VARCHAR(50)
,@sEmailAddress VARCHAR(250)
,@sSocialProfileAddress VARCHAR(250)
,@sPostalAddress VARCHAR(500)
,@iWindowID DECIMAL
,@ifloorID DECIMAL
AS
BEGIN
SET NOCOUNT ON

IF EXISTS (SELECT fk_storeID FROM tblStoreDetails WHERE fk_storeID = @iStoreID)
BEGIN
UPDATE tblStoreDetails
SET
OpensMonday = @sOpensMonday
,ClosesMonday = @sClosesMonday
,OpensTuesday = @sOpensTuesday
,ClosesTuesday = @sClosesTuesday
,OpensWednesday = @sOpensWednesday
,ClosesWednesday = @sClosesWednesday
,OpensThursday = @sOpensThursday
,ClosesThursday = @sClosesThursday
,OpensFriday = @sOpensFriday
,ClosesFriday = @sClosesFriday
,OpensSaturday = @sOpensSaturday
,ClosesSaturday = @sClosesSaturday
,OpensSunday = @sOpensSunday
,ClosesSunday = @sClosesSunday
,TelNumber = @sTelNumber
,FaxNumber = @sFaxNumber
,EmailAddress = @sEmailAddress
,SocialProfileAddress = @sSocialProfileAddress
,PostalAddress = @sPostalAddress
,fk_windowID = @iWindowID
,fk_floorplansID = @ifloorID
WHERE
fk_StoreID = @iStoreID

END
ELSE
BEGIN
INSERT INTO tblStoreDetails (
fk_StoreID
,OpensMonday
,ClosesMonday
,OpensTuesday
,ClosesTuesday
,OpensWednesday
,ClosesWednesday
,OpensThursday
,ClosesThursday
,OpensFriday
,ClosesFriday
,OpensSaturday
,ClosesSaturday
,OpensSunday
,ClosesSunday
,TelNumber
,FaxNumber
,EmailAddress
,SocialProfileAddress
,PostalAddress
,fk_windowID
,fk_floorplansID
)
VALUES (
@iStoreID
,@sOpensMonday
,@sClosesMonday
,@sOpensTuesday
,@sClosesTuesday
,@sOpensWednesday
,@sClosesWednesday
,@sOpensThursday
,@sClosesThursday
,@sOpensFriday
,@sClosesFriday
,@sOpensSaturday
,@sClosesSaturday
,@sOpensSunday
,@sClosesSunday
,@sTelNumber
,@sFaxNumber
,@sEmailAddress
,@sSocialProfileAddress
,@sPostalAddress
,@iWindowID
,@ifloorID
)
END
END

Please can someone have a look at this and tell me if there is a problem with this, or could it be somehting else?

if you need more informetion please shout.

kind regards
MG

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-21 : 11:01:02

It should work. Maybe how you are calling it?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-21 : 11:24:09

This is off topic but as far as I'm concerned your table violates normalization (1st rule). What if you wanted a query: which nights is the store open past 8:00pm?

I would add another table:

Store_Hours: iStoreID, Day_Of_Week, Time_Opens, Time_Closes

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-22 : 03:26:56

Hi dennis,

Thank you for both replies.

You're probably right with regards to normalisation, but I'm new to SQL and have a lot to learn. Everyday is a school day right.

Kind regards
MG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources