Help needed for Creating Stored procedure logic.

• pavanpala.vja

  Mr or Mrs. 500

  Points: 577

  More actions

  April 5, 2017 at 6:05 am

  #342203

  Team,

  IN my database table i have 500 Servers. If server status "1"  means active and live. If server status "0" means its offline server.
  Now i need to create an SP based on my requirement for updating the servers status from "0 to 1 " based on server name .

  Ex : Servr     Status
         ServA      0
         ServB      0
         ServC     1  
         ServD      0

  Now i need to Update Server B & D with status 1. with help of SP. MY Stored procedure should have two parameters.
  1. with Server name(Servr     ) 2. WIth server status(Status).

  Please find the my database table structure with sample data for this.
  ------------------------------------------------------------------------------------------------------
  Table   :

  /****** Object: Table [dbo].[KeepAliveStatus]  Script Date: 4/5/2017 10:25:17 AM ******/
  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

  SET ANSI_PADDING ON
  GO

  CREATE TABLE [dbo].[KeepAliveStatus](
      [ServerName] [varchar](25) NOT NULL,
      [KeepAliveStatusProdA] [tinyint] NOT NULL,
      [KeepAliveStatusProdB] [tinyint] NULL,
      [KeepAliveStatusSoakA] [tinyint] NULL,
      [KeepAliveStatusSoakB] [tinyint] NULL
  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF
  GO
  -------------------------------------------------------------------------

  data :

  USE [Infra_Support]
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y01P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y02P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y03P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y04P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y05P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y06P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y07P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y08P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y09P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y10P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y11P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y12P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y13P', 0, 0, 0, 0)
  GO
  INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y14P', 0, 0, 0, 0)
  GO

  Now i want an SP with 2 parametres which can change the server status from 0 to 1 OR 1 to 0 based on my servername.
  NOte : i need to update the server values in "KeepAliveStatusProdA ' with either 0 or 1. rest all columns can be ignorable.

  Thanks,
  pavan P

  Pavan Kumar. Pala[font="Verdana"][/font]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 5, 2017 at 6:07 am

  #1936759

  Please don't post multiple threads for the same question.
  No replies here. Direct replies to https://www.sqlservercentral.com/Forums/1869043/Help-required-for-creating-the-Stored-procedure-for-below-logic

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass