• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

SQL Is there a way to re-write this stored procedure so it works?

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GetDeviceSettingsByDeviceID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GetDeviceSettingsByDeviceID];
GO
CREATE PROCEDURE [dbo].[GetDeviceSettingsByDeviceID]
(
     @DeviceID As int
)
AS

DECLARE @ChecklistEnabledCommand As varchar(50)
DECLARE @ChecklistEnabled As INT
DECLARE @TimeoutCommand As varchar(50)
DECLARE @Timeout As INT
DECLARE @TimeoutActionCommand As varchar(50)
DECLARE @TimeoutAction As varchar(50)
DECLARE @CanStartCommand As varchar(50)
DECLARE @CanStart As varchar(50)

SELECT @ChecklistEnabledCommand = 'CHKENDIS'
SELECT @ChecklistEnabled = (SELECT [ChecklistEnabled] FROM [501ChecklistSettings] WHERE [DeviceID] = @DeviceID)
SELECT @TimeoutCommand = 'CHUPTOUT'
SELECT @Timeout = (SELECT [Timeout] FROM dbo.[501ChecklistSettings] WHERE [DeviceID] = @DeviceID)
SELECT @TimeoutActionCommand = 'CUPOTOUT'
SELECT @TimeoutAction = (SELECT [TimeoutAction] FROM [501ChecklistSettings] WHERE [DeviceID] = @DeviceID)
SELECT @CanStartCommand = 'CHCANSTR'
SELECT @CanStart = (SELECT [CanStart] FROM [501ChecklistSettings] WHERE [DeviceID] = @DeviceID)


BEGIN
UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @ChecklistEnabledCommand,
            [Value] = @ChecklistEnabled,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @ChecklistEnabledCommand AND [Value] <> @ChecklistEnabled

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @ChecklistEnabledCommand,
        @ChecklistEnabled,
        'NEW'
     )

UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @TimeoutCommand,
            [Value] = @Timeout,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @TimeoutCommand AND [Value] <> @Timeout

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @TimeoutCommand,
        @Timeout,
        'NEW'
     )

UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @TimeoutActionCommand,
            [Value] = @TimeoutAction,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @TimeoutActionCommand AND [Value] <> @TimeoutAction

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @TimeoutActionCommand,
        @TimeoutAction,
        'NEW'
     )

UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @CanStartCommand,
            [Value] = @CanStart,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @CanStartCommand AND [Value] <> @CanStart

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @CanStartCommand,
        @CanStart,
        'NEW'
     )
END
GO
0
MBoy
Asked:
MBoy
1 Solution
 
DerZaubererCommented:
It's not so easy to understand what that SP is supposed to do if it works 'correctly'.
Do you get errors? Maybe add some explanation what this is supposed to mean ...
0
 
Pratima PharandeCommented:
yes SP looks good , only I think you need to change BEgin position to after AS

like below
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GetDeviceSettingsByDeviceID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GetDeviceSettingsByDeviceID];
GO
CREATE PROCEDURE [dbo].[GetDeviceSettingsByDeviceID]
(
     @DeviceID As int
)
AS
BEGIN
DECLARE @ChecklistEnabledCommand As varchar(50)
DECLARE @ChecklistEnabled As INT
DECLARE @TimeoutCommand As varchar(50)
DECLARE @Timeout As INT
DECLARE @TimeoutActionCommand As varchar(50)
DECLARE @TimeoutAction As varchar(50)
DECLARE @CanStartCommand As varchar(50)
DECLARE @CanStart As varchar(50)

SELECT @ChecklistEnabledCommand = 'CHKENDIS'
SELECT @ChecklistEnabled = (SELECT [ChecklistEnabled] FROM [501ChecklistSettings] WHERE [DeviceID] = @DeviceID)
SELECT @TimeoutCommand = 'CHUPTOUT'
SELECT @Timeout = (SELECT [Timeout] FROM dbo.[501ChecklistSettings] WHERE [DeviceID] = @DeviceID)
SELECT @TimeoutActionCommand = 'CUPOTOUT'
SELECT @TimeoutAction = (SELECT [TimeoutAction] FROM [501ChecklistSettings] WHERE [DeviceID] = @DeviceID)
SELECT @CanStartCommand = 'CHCANSTR'
SELECT @CanStart = (SELECT [CanStart] FROM [501ChecklistSettings] WHERE [DeviceID] = @DeviceID)



UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @ChecklistEnabledCommand,
            [Value] = @ChecklistEnabled,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @ChecklistEnabledCommand AND [Value] <> @ChecklistEnabled

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @ChecklistEnabledCommand,
        @ChecklistEnabled,
        'NEW'
     )

UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @TimeoutCommand,
            [Value] = @Timeout,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @TimeoutCommand AND [Value] <> @Timeout

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @TimeoutCommand,
        @Timeout,
        'NEW'
     )

UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @TimeoutActionCommand,
            [Value] = @TimeoutAction,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @TimeoutActionCommand AND [Value] <> @TimeoutAction

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @TimeoutActionCommand,
        @TimeoutAction,
        'NEW'
     )

UPDATE [dbo].[DeviceSettings]
     SET
        [ItemName] = @CanStartCommand,
            [Value] = @CanStart,
            [State] = 'EDIT'
     WHERE
         [DeviceID] = @DeviceID AND [ItemName] = @CanStartCommand AND [Value] <> @CanStart

IF @@ROWCOUNT=0
    INSERT INTO  [DeviceSettings]
     (
            [DeviceID],
            [ItemName],
        [Value],
        [State]
     )
     VALUES
     (
            @DeviceID,
            @CanStartCommand,
        @CanStart,
        'NEW'
     )
END
GO

Open in new window

0
 
Lee SavidgeCommented:
Move the BEGiN so it is below the AS keyword
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now