Can the number of stored procedure parameters be variable?

[ajliles]

Hi,

Is it possible to call a stored procedure and pass to it a number of parameters that can vary at run-time (from 1 upto the max 2100). I would like the stored procedure to be flexible enough to determine how many parameters are present and then build up a where clause within a loop. I know that I could declare all 2100 parameters within the SP and then check for a null value within the loop, but is there a cleaner solution?

Thanks,
Adrian.

 

[JamesLean]

No, you have to declare all the parameters in the CREATE PROCEDURE statement. AS you say, you could set a default of NULL on each one and check for values in the body of the proc. This could get complicated if you think you might have 2100!! --James

 

[Kudel]

Hi

Maybe you can write all the parameters to a table, one at the time, an then run a proc that uses the information in the table.

create proc test @parameter
as
insert into table values(@Parameter)
end

Then put "test" in a loop. When the loop ends you execut the other proc. Finaly you empty the table.

-Kudel

 

[JNeave]

What do you need all 2100 parameters for? Creating a where clause?

Tell us more about the sproc and we might think of a better way of doing this.

Jim.

 

[ajliles]

Okay, thanks for the help so far.

I'm allowing the user of my web application to define their own database query (consisting of as many sub-queries as they like, all linked by an OR). The user can then search the database and if necessary remove or edit a sub-query. Each sub-query could be a where clause in it's own right, but by having one where clause I would be ensuring that records are not selected more than once.

EXEC ('SELECT * FROM table WHERE ' + @query1 + ' OR ' + @query2 + ' OR ' + @queryN)

The problem is that I'm restricted to either making sure that the entire query is passed as one parameter (ie, 8000 byte limit) or having a parameter for each sub-query. I do not know in advance how many sub-queries will be required as the user could request a very complex report - although realistically I could limit this to 100 or so, I was wondering if their was a nicer solution.

I don't believe I can use a temp table to store each parameter, as theoretically many users may be requesting reports at the same time - it could get a bit complicated.

I may have to impose my own limit of 100 sub-queries, and loop through these until I hit null.

Adrian.

 

[Kudel]

Maybe if you put a userID/queryID as a parameter into the table as well?

 

[ajliles]

Many thanks, everyone.

I think I will try the loop-until-null solution. In my experience, temp tables can cause many performance issues, bottlenecks, etc, so I will try other options first.

Adrian.

 

[SQLSister]

Building a dynamic where clause with 2100 possible variables is not practical at best. I have one with 30 variables and it is 16 pages long. To process that number of variables I suspect you would run up against the limit of bytes in a stored procedure plus it would get so complicated to build (you'll have all kinds of joins to consider as well as the where clauses) that you will have a great deal of trouble following your own logic and debugging would be a nightmare. How many combinations would you have to test to make sure it really worked? Even a 100 is way too many to program this way. And how often would anyone want to do a query that large anyway?

This is a case where you really need to go talk to your users and get more information about what they really need.

What makes more sense to me is to discuss with your users the queries they are most interested in and creating those as standard queries. Then build a standard query page that lets them specify some limited parameters on these queries (like start and end date or customer ID). Then build specialized advanced query pages for different user groups (or subjects) which have the fields they are most interested in as choices. Yeah it sounds like more work to do, but I bet it would go faster and run more efficiently if you can break this up into chunks that the users really want to query.

Plus I think you'll find that most users aren't interested in 98% of the 2100 fields when they query. At least not in the where clause.

Another thing you could do is create a page where they select the tables they want (which you use to build the joins) then they are given a dynamic list of fields to choose from and and a box next to the field choice for the parameter data. Limit the number of choices they could choose for the where clause to ten (in reality I'd probably go no more than 5, but you'd need to really know what data your users want to determine the upper limit), this is manageable and no one will get the data they want if they try to specify more than that in the where clause anyway. Then give them another box to use to select the fields they want in the query based on the tables they selected (who wants to go through a list of 2100 variables to choose?)

Another thing you have to consider is most users are not capable of framing a good query because they don't understand boolean logic. Faced with a a variable list and a blank box for putting in the parameters and a choice of and/or and =,<,>, <> they don't know what to choose to get the results they want. Better to lead them in the user interface than to give them too much choice in most cases. Plus anything that will be run often needs to be saved somehow so they don't have to keep recreating it. That's one reason why having a standard set of common queries is a good idea. These can be optimized for processing speed (which the dynamic SQL cannot). And they save time for the users too. Which most users prefer.

If you really want to see why building a dynamic where clause with 2100 variables (or even a 100) is impractical, I'll be glad to email you my stored procedure that queries only 30. Just send me an email.

 

[ajliles]

Thanks for your help. I've pasted the current SP below, which is fairly rigid in order for the execution to be as efficient as possible (without resorting to dynamic SQL). At the moment this works very well, but I'm now trying to change this so that the @Type, @GroupID and @Criteria can be repeated up to 50 times - with one single result set returned and no duplicate records. Calling the same SP over and over again for each sub-query and appending the resulting data to a temp table, then returning distinct records would be one solution, but creating temp tables seems to send the cpu usage way too high for my liking.


CREATE PROCEDURE [dbo].[spr_ratings]
(
@Type char(1),
@GroupID int,
@Criteria varchar(7500),
@GroupBy char(1)
)
AS
set nocount on

IF @Criteria = '<all>'
BEGIN
IF @GroupBy = '1'
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE dbo.tbl_07_Employee.GroupID = @GroupID
ORDER BY dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName
ELSE
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE dbo.tbl_07_Employee.GroupID = @GroupID
ORDER BY dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName

GOTO Label1
END

IF @Type = '1'
BEGIN
IF @GroupBy = '1'
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (dbo.tbl_07_Employee.FirstName + ' ' + dbo.tbl_07_Employee.LastName like @Criteria + '%')
ORDER BY dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName
ELSE
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (dbo.tbl_07_Employee.FirstName + ' ' + dbo.tbl_07_Employee.LastName like @Criteria + '%')
ORDER BY dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName

GOTO Label1
END

IF @Type = '2'
BEGIN
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (dbo.tbl_07_Employee.EmployeeNo = @Criteria)

GOTO Label1
END

IF @Type = '3'
BEGIN
IF @GroupBy = '1'
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (CHARINDEX(CAST(dbo.tbl_04_Reviewer.ReviewerID AS varchar),@Criteria) > 0)
ORDER BY dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName
ELSE
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (CHARINDEX(CAST(dbo.tbl_04_Reviewer.ReviewerID AS varchar),@Criteria) > 0)
ORDER BY dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName

GOTO Label1
END

IF @Type = '4'
BEGIN
IF @GroupBy = '1'
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (CHARINDEX(', ' + dbo.tbl_05_Location.Channel + '/' + dbo.tbl_05_Location.Department + ',',', ' + @Criteria + ',') > 0)
ORDER BY dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName
ELSE
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (CHARINDEX(', ' + dbo.tbl_05_Location.Channel + '/' + dbo.tbl_05_Location.Department + ',',', ' + @Criteria + ',') > 0)
ORDER BY dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName

GOTO Label1
END

IF @GroupBy = '1'
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (CHARINDEX(', ' + dbo.tbl_06_Title.Title + ',',', ' + @Criteria + ',') > 0)
ORDER BY dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName
ELSE
SELECT dbo.tbl_05_Location.Channel, dbo.tbl_05_Location.Department, dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName, dbo.tbl_07_Employee.EmployeeNo, dbo.tbl_06_Title.Title, dbo.tbl_07_Employee.Gender, dbo.tbl_07_Employee.HireDate, dbo.tbl_07_Employee.Score1, dbo.tbl_07_Employee.Score2, dbo.tbl_07_Employee.Score3, dbo.tbl_07_Employee.Score4, dbo.tbl_07_Employee.Score5, dbo.tbl_07_Employee.Score6, dbo.tbl_07_Employee.Score7, dbo.tbl_07_Employee.RatingDate, dbo.tbl_07_Employee.ReturnedRatingCard
FROM dbo.tbl_04_Reviewer INNER JOIN dbo.tbl_05_Location ON dbo.tbl_04_Reviewer.ReviewerID = dbo.tbl_05_Location.ReviewerID INNER JOIN dbo.tbl_07_Employee ON dbo.tbl_05_Location.LocationID = dbo.tbl_07_Employee.LocationID INNER JOIN dbo.tbl_06_Title ON dbo.tbl_07_Employee.TitleID = dbo.tbl_06_Title.TitleID
WHERE (dbo.tbl_07_Employee.GroupID = @GroupID) AND (CHARINDEX(', ' + dbo.tbl_06_Title.Title + ',',', ' + @Criteria + ',') > 0)
ORDER BY dbo.tbl_04_Reviewer.ReviewerName, dbo.tbl_07_Employee.FirstName, dbo.tbl_07_Employee.LastName

Label1:
set nocount off
GO

 

[cheyney]

Look at this to pass a comma separated list to a stored procedure:

http://www.sqlteam.com/item.asp?ItemID=637

cheyney