A common way to pass large sets of data to a stored procedure is to use an XML or CSV character string.
This can work, but it can be complicated to encode and then decode the strings passed in, and you don’t have a clean way to validate the data as it is passed.
First, we need a parameter table for this report: create table Param_Customer Report Filter ( Parameter Key int references Parameter Keys(Parameter Key) on delete cascade, Customer ID int references Customers (Customer ID) on delete cascade, Sort int not null, Comment varchar(1000), Primary key (Parameter Key, Customer ID) ) As before, you should add some error checking and a return value to this stored procedure to inform the client if any problems arose when preparing the parameter data.
Finally, our report will accept a @Parameter Key parameter and join to the Param_Customer Report Filter table to create its results: Our client application simply needs to keep track of the customers that the user wants to see on the report, along with the sort and any notes for each.
SQL Server 20 do have a table data type, but you cannot pass a table variable to a stored procedure as an argument, so that doesn’t work.
So, is there any easy was a way for a stored procedure to accept really complicated data, and for that data to be completely validated with foreign key constraints, strong data types, and possibly even CHECK constraints?
My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP.
Check in frequently for tips, tricks, commentary and ideas on SQL Server and . Subscribe SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure.
Of course, this example can easily be expanded to include other attributes of the customer as well, such as the customer Name or perhaps a customer type.
Our stored procedure will accept a set of rows with two columns: Customer ID and Status ID.
The cascading delete constraint immediately removes all parameter data for any old keys, all at once, from any and all parameter tables in your database.
We also can create a stored procedure to delete the data for a particular parameter key, which should be called when you are done using the parameter values: It’s as simple as that!
When the user is done and the report is requested, you simply get a parameter key, load up the Param_Customer Report Filter table using either SQLBulk Copy or the Param_Customer Report Filter Add stored procedure, and then pass in that key when you finally call the Customer Report stored procedure to return the report data.