pass multiple values into stored procedure

Question

pass multiple values into stored procedure

wendy elizabeth

SSCoach

Points: 16915
More actions
October 1, 2018 at 11:39 am

#363591

In t-sql-2012, I am trying to load all the schoolID values into the variable called @CalendarID.
I then want to pass all the values into a parameter called @schoolID into a stored procedure called procCalendarS.
The dbo.fn_splitString is a string split function at my company where you can split a string of values.
The parameter going into the stored procedure called schoolid is declared as nvarchar(max).
Here is the sql.
declare @calendarID VARCHAR (8000) = (SELECT schoolID from dbo.school)
DECLARE @return_value int
EXEC @return_value = campusOPS.[dbo].[procCalendarS]
@schoolID = SELECT * FROM dbo.fn_splitString(@CalendarID),
@endYear = N'2019'
SELECT 'Return Value' = @return_value
can you show me how to pass all the values called schoolid from the school table into the @schoolID paramter?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246726 More actions · Answer 1

wendy elizabeth - Monday, October 1, 2018 11:39 AM
In t-sql-2012, I am trying to load all the schoolID values into the variable called @CalendarID.
I then want to pass all the values into a parameter called @schoolID into a stored procedure called procCalendarS.
The dbo.fn_splitString is a string split function at my company where you can split a string of values.
The parameter going into the stored procedure called schoolid is declared as nvarchar(max).
Here is the sql.
declare @calendarID VARCHAR (8000) = (SELECT schoolID from dbo.school)
DECLARE @return_value int
EXEC @return_value = campusOPS.[dbo].[procCalendarS]
@schoolID = SELECT * FROM dbo.fn_splitString(@CalendarID),
@endYear = N'2019'
SELECT 'Return Value' = @return_value
can you show me how to pass all the values called schoolid from the school table into the @schoolID paramter?

Why not use a table-valued parameter instead?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

Would you show me how to write table-valued parameter for the example that I just showed to you?

sgmunson SSC Guru Points: 110639 More actions · Answer 3

wendy elizabeth - Monday, October 1, 2018 12:41 PM
Would you show me how to write table-valued parameter for the example that I just showed to you?

Before you worry about creating a table-valued parameter, do you have the ability to change the stored procedure to actually be able to make use of such a parameter?
Additionally, how many values are in the school table? If you have any sizable number, performance may rear its ugly head, as table variables are always estimated to have exactly one row, so it throws off the estimates for the optimizer every time you have more than one row. I don't use table variables for that reason. The key here, however, is that the data type of the stored procedure has to match the data type of the parameter being supplied, and it that procedure is used for other code in your company, you can't just change that without having to change what could be any number of other pieces of code that rely on it. You might be better served by adding a new stored procedure that accesses the school table directly. If you can provide more detail, we can help you figure out how to deal with it.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110639 More actions · Answer 4

wendy elizabeth - Monday, October 1, 2018 11:39 AM
In t-sql-2012, I am trying to load all the schoolID values into the variable called @CalendarID.
I then want to pass all the values into a parameter called @schoolID into a stored procedure called procCalendarS.
The dbo.fn_splitString is a string split function at my company where you can split a string of values.
The parameter going into the stored procedure called schoolid is declared as nvarchar(max).
Here is the sql.
declare @calendarID VARCHAR (8000) = (SELECT schoolID from dbo.school)
DECLARE @return_value int
EXEC @return_value = campusOPS.[dbo].[procCalendarS]
@schoolID = SELECT * FROM dbo.fn_splitString(@CalendarID),
@endYear = N'2019'
SELECT 'Return Value' = @return_value
can you show me how to pass all the values called schoolid from the school table into the @schoolID paramter?

This specific code's problem is that you are attempting to assign ALL the values from the school table to a variable designed to hold only one value. For that reason, the last value selected will be the only one that ends up in @calendarID. You then try to do the SELECT directly as part of the parameter specification in the EXECUTE, and that will fail because EXECUTE expects a single value for the parameter because that's what the stored procedure is expecting. Finally, it seems odd to me that a year value would for some reason need to be an nvarchar value instead of an integer. As I stated before, you'd need to be able to change the stored procedure before it would be able to handle multiple values.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mike01 SSChampion Points: 11393 More actions · Answer 5

Steve is right. This is only selecting one record. declare @calendarID VARCHAR (8000) = (SELECT schoolID from dbo.school)

You would need something like this to get all the values
declare @calendarID VARCHAR (8000) = ''
select @Calendar = @Calendar + (cast(SchoolID as varchar(20)) + ',' from dbo.School

More to the point though, is if you are getting every value in the school table, why not add the join to School in the procedure you are calling?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/