Solved

Need to accept a Workstation list using SQL SP w/ Parameter

Posted on 2009-03-31
5
187 Views
Last Modified: 2012-05-06
I need to setup a new SQL Stored Procedure to use a Parameter (call it @Endpoints) to accept a list of workstation machines from another Devloper who hasn't told me how he will pass the workstation names to me.
He said he'll send the workstation names to me and said to setup a SQL SP that accepts machines as a parameter. Below is some code I use currently. It appears the only thing that will probably need to be updated is the usp_GetNodes Stored Procedure. Below is the C# Code and below that is the SP. How can I setup the SP to receive a list of workstations that I don't know how they' ll be sent?
Thanks Experts,
Wally
------------------- C# CODE ------------------------------------------------------------------
public string nodeToPing;
        public string NodeToPing
        {
            get { return nodeToPing; }
            set { nodeToPing = value; }
        }
 
        static void Main(string[] args)
        {
 
            // Do Database Query Here.....
            // This will hold the entire workstation 
            // list that is pulled from the Nodes table
            List<Program> nodeList = new List<Program>();
 
            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
            try
            {
                conn.Open();
                Console.WriteLine("DB Connection has been opened\n");
            }
            catch
            {
                Console.WriteLine("Database connection failed");
                return;
            }
 
            SqlCommand recCount = new SqlCommand("SELECT Count(*) FROM Nodes", conn);
            UInt32 totalRows = Convert.ToUInt32(recCount.ExecuteScalar());
            SqlCommand sqlCmd = new SqlCommand("usp_GetNodes", conn);
            sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataReader reader = null;
            reader = sqlCmd.ExecuteReader();
 
            Console.WriteLine(String.Format("There are {0} records in the database.", totalRows));
            // Store server data into a collection
            if (reader.HasRows)
            {
                //int currentRow = 0;
                while (reader.Read())
                {
                    Program node = new Program();
                    node.NodeToPing = reader.GetString(0);
                    nodeList.Add(node);
                    Console.WriteLine(node.NodeToPing);
                    //currentRow++;
                    //Console.WriteLine(String.Format("{0}% Complete", currentRow * 100 / totalRows));
                }
 
                reader.Close();
 
------------------------- STORED PROCEDURE -----------------------------------------------
USE [DMS]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetNodes]    Script Date: 03/31/2009 09:59:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[usp_GetNodes]
	-- variables
	-- @Endpoints varchar(75) = '%'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- SELECT n.Name FROM Nodes n WHERE n.Name LIKE @Endpoints
	SELECT Name FROM Nodes
END

Open in new window

0
Comment
Question by:wally_davis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24032060
Is the list of workstations gonna be based on a pattern match or an exact match?

Exact matches can be handled by using just a single parameter declared as varchar(8000) using dynamic SQL as long as individual machine names are separated by commas (CSV string)

Pattern Matches can be slightly tricky as it is likely that you will need a UDF to parse individual items in the string to a table and then use the like operator to join on this dynamic table..

0
 

Author Comment

by:wally_davis
ID: 24032297
an exact match.
0
 

Author Comment

by:wally_davis
ID: 24032341
FYI, I've never used Dynamic SQL. Where's a good resource to learn it and do you have any examples?
0
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24032696
Try the adapted stored procedure below -

You will have to pass in workstation names in a csv list. The script below uses a variable to build the SQL statement including wrapping the contents in single quotes and uses the IN keyword to match the list..
ALTER PROCEDURE [dbo].[usp_GetNodes]
         @Endpoints varchar(8000) = NULL   -- Pass comma separated values here like 'WorkStation1,WorkStation2'
AS
BEGIN
        SET NOCOUNT ON
	DECLARE @SQL varchar(8000)
	SET @SQL = 'SELECT [Name] FROM Nodes WHERE [Name] IN (''' + REPLACE(@EndPoints,',',''',''') + ''')'
        EXEC	(@SQL)
END
GO

Open in new window

0
 

Author Closing Comment

by:wally_davis
ID: 31564956
Thanks Reb73 for your help and the walk-through. This is a great start for what I need to do. :)
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question