April 5, 2017 at 5:52 am
HI TEam.
Failed to create the store procedure with 2 input parameters for the below logic. Can some one please help me on this.
Table structure :
USE [Infra_Support]
GO
/****** 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
Table 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 the logic i have is :
declare @ListOfServers varchar(MAX) = '''MCSWEB-WC-A1P'',''MCSWEB-WC-A2P'',''MCSWEB-PO-A1P'',''MCSWEB-PO-A2P'''
declare @cmd varchar(max)
declare @a char = 1
set @cmd = 'update Infra_Support.dbo.keepalivestatus set KeepAliveStatusProdA = ' + @a + ' where servername in (' + @ListOfServers + ')'
select @cmd
exec (@cmd)
for above logic i need to create stored procedure with two input parameters.
one With server name(ListOfServers ) and second with server status(@A)
sample output for SP should be like below :
EXEC @return_value = [dbo].[Testing]
@ListOfServers = N'MCSWEB-WC-A1P,MCSWEB-PO-A1P',
@B = N'1'
SELECT 'Return Value' = @return_value
can some one please help me.
April 5, 2017 at 6:33 am
pavanpala.vja - Wednesday, April 5, 2017 5:52 AMHI TEam.Failed to create the store procedure with 2 input parameters for the below logic. Can some one please help me on this.
Table structure :
USE [Infra_Support]
GO/****** Object: Table [dbo].[KeepAliveStatus] Script Date: 4/5/2017 10:25:17 AM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE 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
GOTable 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 the logic i have is :
declare @ListOfServers varchar(MAX) = '''MCSWEB-WC-A1P'',''MCSWEB-WC-A2P'',''MCSWEB-PO-A1P'',''MCSWEB-PO-A2P'''
declare @cmd varchar(max)
declare @a char = 1
set @cmd = 'update Infra_Support.dbo.keepalivestatus set KeepAliveStatusProdA = ' + @a + ' where servername in (' + @ListOfServers + ')'
select @cmd
exec (@cmd)for above logic i need to create stored procedure with two input parameters.
one With server name(ListOfServers ) and second with server status(@A)sample output for SP should be like below :
EXEC @return_value = [dbo].[Testing]
@ListOfServers = N'MCSWEB-WC-A1P,MCSWEB-PO-A1P',
@B = N'1'
SELECT 'Return Value' = @return_valuecan some one please help me.
Other than the obvious potential for SQL Injection, what's the problem? Converting that code into a stored procedure isn't difficult. If you want to improve the code, I suggest that you use a splitter instead of dynamic SQL. One of the best splitters is shared and explained in here: http://qa.sqlservercentral.com/articles/Tally+Table/72993/
April 5, 2017 at 6:54 am
Have you made any attempts to code this yourself? First thing I see is that with your design, you're leaving yourself wide open to something known as SQL injection. What if someone (let's assume someone dangerous, like a hacker) decides to feed something other than a server list to this stored procedure? With that kind of idea of just using dynamic SQL, you're opening the door for the bad guy to potentially execute almost any SQL they can come up with. If you're going to insist on using a comma-separated list of server names, then at least have a mechanism for validating that your input to the procedure is actually valid. Let's also avoid allowing a varchar(max) type of value, as that's like saying you need a list of server names that might grow to some 40 million...
Do you have a table with a list of valid server names? Maybe you need one.. Let's presume you DO have one. Something like the following might work:
(it's probably good up to around 400 server names at 20 chars per name, or up to 800 with 10 chars per...)CREATE TABLE [dbo].[KeepAliveStatus] (
[ServerName] [varchar](25) NOT NULL,
[KeepAliveStatusProdA] [tinyint] NOT NULL,
[KeepAliveStatusProdB] [tinyint] NULL,
[KeepAliveStatusSoakA] [tinyint] NULL,
[KeepAliveStatusSoakB] [tinyint] NULL
);
INSERT INTO [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB])
VALUES (N'CDTAPP-AS-Y01P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y02P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y03P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y04P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y05P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y06P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y07P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y08P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y09P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y10P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y11P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y12P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y13P', 0, 0, 0, 0),
(N'CDTAPP-AS-Y14P', 0, 0, 0, 0);
GO
CREATE PROCEDURE dbo.UpdateKeepAliveStatus (
@ServerList varchar(8000),
@NewValue tinyint
)
AS
BEGIN
SET NOCOUNT ON;
UPDATE KAS
SET KAS.KeepAliveStatusProdA = @NewValue
FROM dbo.KeepAliveStatus AS KAS
CROSS APPLY dbo.DelimitedSplit8K(@ServerList, ',') AS S
WHERE KAS.ServerName = S.Item;
RETURN @@ROWCOUNT;
END
GO
There are a couple of things that you didn't mention in your post. You seem to want a return value, but what does one return? I chose to return the number of rows that got updated. It would be a lot more complicated to return more information than that, but not necessarily impossible or impractical. You also didn't really say what your overall objective was. Lastly, I relied on a function you can find here on this site by searching the articles for "Tally Oh". The article by Jeff Moden shows how to split a delimited string efficiently, and that function is shown in the above stored procedure. It's use also helps protect against SQL injection because it's going to parse that input string and separate it into pieces by the specified delimiter. Your thoughts?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply