MS SQL Pass An Array Or Table From VB Via Stored Procedure

Greetings Gurus:

I'm working on a softball league and need to pass an array or table of team numbers (int) from my vb.net code to a stored procedure as a parameter. The stored procedure will use this data in a WHERE intTeam IN (<array>|<table>). Currently, I am executing the procedure for each team. There may be as many as several hundred teams.

The stored procedure currently looks like:
ALTER PROCEDURE [dbo].[up_s_StandingTotalSeasonalRuns] 
	-- Add the parameters for the stored procedure here
	@intTeamNumber			int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS	(SELECT * 
					FROM sys.objects
					WHERE object_id = OBJECT_ID(N'#TeamScores'))		
		DROP TABLE #TeamScores

		SELECT		fkintTeam1 AS intTeam,
					SUM(intScore1) AS intTeamVsTeamTotal
			INTO		#TeamScores
			FROM		Schedule
			WHERE		fkintTeam1 IN
							(@intTeamNumber)
			GROUP BY	fkintTeam1

	UNION		ALL

		SELECT		fkintTeam2 AS intTeam,
					SUM(intScore2) AS intTeamVsTeamTotal
			FROM		Schedule
			WHERE		fkintTeam2 IN
							(@intTeamNumber)
			GROUP BY	fkintTeam2
			ORDER BY	intTeam

	SELECT		intTeam,
				SUM(intTeamVsTeamTotal) AS intTeamVsTeamTotal
		FROM		#TeamScores
		GROUP BY	intTeam
END

Open in new window

The procedure reads schedule records which have the following columns:
	[intScheduleId] [int] IDENTITY(1,1) NOT NULL,
	[fkintFieldNo] [int] NULL,
	[dtPlay] [datetime] NOT NULL,
	[fkintTeam1] [int] NULL,
	[intScore1] [int] NOT NULL,
	[fkintTeam2] [int] NULL,
	[intScore2] [int] NOT NULL,
	[blnGamePlayed] [bit] NOT NULL,
	[blnByeWeek] [bit] NOT NULL,
	[blnHoliday] [bit] NOT NULL,
	[blnMakeUp] [bit] NOT NULL,
	[blnDoubleHeader1] [bit] NOT NULL,
	[blnDoubleHeader2] [bit] NOT NULL,
	[dtCreated] [datetime] NOT NULL,
	[dtUpdated] [datetime] NOT NULL,
	[tsValue] [timestamp] NOT NULL

Open in new window

Any assistance you might provide is most appreciated.

Much thanks,
David Bach
David BachAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Hi,

To achieve this, you can also use one temporary transaction table and each time only pass relevant record IDs, which are for one or more records.
0
David BachAuthor Commented:
Greetings HuaMinChen:

I appreciate your prompt response.

Might you please send me an example of your solution?


Much thanks,
David Bach
0
HuaMin ChenProblem resolverCommented:
You need to pass several column values to Stored proc, right? So you can then pass a record ID (that can be declared as INT) to it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David BachAuthor Commented:
Greetings HuaMinChen:

Again, I appreciate your prompt response.

Currently, looking at the stored procedure I am passing a single integer of @intTeamNumber.

I do not know how to pass a column of information from vb.net to MS SQL via a stored procedure parameter.

If you would please give me an example of what I need to add/change/delete from the stored procedure I would be most greatful.


Much thanks,
David Bach
0
HuaMin ChenProblem resolverCommented:
Hi,
See example below to call Stored proc, from .Net
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection connection ;
            SqlDataAdapter adapter ;
            SqlCommand command = new SqlCommand();
            SqlParameter param ;
            DataSet ds = new DataSet();

            int i = 0;

            connetionString = "Data Source=servername;Initial Catalog=PUBS;User ID=sa;Password=yourpassword";
            connection = new SqlConnection(connetionString);

            connection.Open();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SPCOUNTRY";

            param = new SqlParameter("@COUNTRY", "Germany");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ());
            }

            connection.Close();
        }
    }
}

Open in new window

0
Snarf0001Commented:
"Back in the day", this used to be done by passing in a string of comma separated values and creating a .split function on sql.
Fortunately now, the best approach is to create a custom table type on the database, and pass that directly into the proc.  Better on resources and no messy string conversions.

1: Create the custom table type on the database (keep the names generic as you'll often end up reusing for a wide variety of things)
create type dbo.IntArray as Table (ID int)

Open in new window


2: Change your proc to expect the custom type as the input parameter (note, readonly is required on the param deceleration):
ALTER PROCEDURE [dbo].[up_s_StandingTotalSeasonalRuns] 
	@TeamIDs dbo.IntArray readonly
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS	(SELECT * 
					FROM sys.objects
					WHERE object_id = OBJECT_ID(N'#TeamScores'))		
		DROP TABLE #TeamScores

		SELECT		fkintTeam1 AS intTeam,
					SUM(intScore1) AS intTeamVsTeamTotal
			INTO		#TeamScores
			FROM		Schedule
			WHERE		fkintTeam1 IN
							(select ID from @TeamIDs)	--now you can optionally just to a join as well instead of the "in" clause
			GROUP BY	fkintTeam1

	UNION		ALL

		SELECT		fkintTeam2 AS intTeam,
					SUM(intScore2) AS intTeamVsTeamTotal
			FROM		Schedule
			WHERE		fkintTeam2 IN
							(select ID from @TeamIDs)	--now you can optionally just to a join as well instead of the "in" clause
			GROUP BY	fkintTeam2
			ORDER BY	intTeam

	SELECT		intTeam,
				SUM(intTeamVsTeamTotal) AS intTeamVsTeamTotal
		FROM		#TeamScores
		GROUP BY	intTeam
END

Open in new window


3: To call from code, you pass the INCOMING data as a datatable, and specify .Structured as the data type.  The only thing that would really change here from your existing code is the parameter parts, but including some filler code for context:
        public static DataSet StandingResults(int[] teamIDs)
        {
            using (var conn = new SqlConnection("-- connection string --"))
            using (var com = new SqlCommand("dbo.up_s_StandingTotalSeasonalRuns", conn))
            {
                com.CommandType = CommandType.StoredProcedure;

                //create the table type param
                var dt = new DataTable();
                dt.Columns.Add("ID", typeof(int));
                foreach (var id in teamIDs)
                    dt.Rows.Add(id);

                var tParam = new SqlParameter("@TeamIDs", dt);
                tParam.SqlDbType = SqlDbType.Structured;
                com.Parameters.Add(tParam);

                //rest of the execute code
                //return the dataset or however you're doing it already
            }
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BachAuthor Commented:
Greetings Snarf0001:

Thank you for your response.

My T-SQL is self-taught. I learned a few things from your example.

Your response was exactly what I needed. I was able to modify my VB code and stored procedure after defining a custom data type (which I didn't know I could do).

Thank you very much for you time and effort in solving my issue.

David Bach
0
David BachAuthor Commented:
Thank you kindly!

David Bach
0
Snarf0001Commented:
You're very welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.