创建一个参数数量不定的存储过程 [英] Creating a stored procedure with an indefinite number of parameters

问题描述

我必须制作一个接受无限数量参数的插入过程，例如

I have to make a insert procedure that takes indefinite number of arguments so for example

USER_ID, First_Name, Last_Name, Fav film, 'Fav Book', Fav Music

EXEC dbo.sp_whatever 'ID999', 'Tommy', 'Soprano', 'Jack', 'Forest Gump', 'Book_name', 'Music_Name'

或

USER_ID, First_Name, Last_Name, Fav film

EXEC dbo.sp_whatever 'ID999', 'Ashley', 'Collins', 'Cujo'

然后根据给定的参数，它应该失败或继续并根据提供的参数在多个表中创建新条目(不言自明).

Then depending on the arguments given it should either fail or go ahead and create new entries in several tables depending on the arguments provided (self explanatory).

现在我完全不知道如何解决这个问题，我如何处理无限数量的参数，或者什么是解决这个问题的正确方法?

Now I have absolutely no idea how to approach this, how can I work on indefinite number of parameters, or what would be the right, proper way to approach this?

基本上.这将是场景.我写了一个存储过程，称之为InsertNewUser"我有一个我想注册的人，所以我执行EXEC InsertNewUser ..."我将有关此人的所有相关信息插入正确的表格中.这基本上就是我想要实现的目标.(我知道剧情很蠢，别担心是编的.)

Basically. This would be the scenario. I write a store procedure call it 'InsertNewUser' I have a person that I want to register, so i do 'EXEC InsertNewUser ...' and I INSERT all the relevant information about this person into the right tables. This is basically what I'm trying to achieve. (I know scenario is stupid, don't worry is made up.)

推荐答案

以下代码演示了在存储过程中处理可选参数的方法.必须事先知道所有可能的参数，但可以使用参数子集调用 SP.

The following code demonstrates a means of handling optional parameters in a stored procedure. All of the possible parameters must be known in advance, but the SP can be called with a subset of parameters.

create procedure AddUser
  @UserId Int Output,
  @FirstName NVarChar(64),
  @LastName NVarChar(64),
  @FavoriteHandbag NVarChar(64) = NULL,
  @FavoriteShoe NVarChar(64) = NULL,
  @FavoriteWeapon NVarChar(64) = NULL
as
  -- Validate the inputs.
  if ( @FavoriteHandbag is NULL ) and ( @FavoriteShoe is NULL ) and ( @FavoriteWeapon is NULL )
    begin
    RaIsError( 'New users must have at least one favorite specified.', 13, 0 );
    return;
    end
  -- tbd: Check for missing or duplicate name, ... .

  -- Store the data.
  insert into Users ( FirstName, LastName, FavoriteHandbag, FavoriteShoe, FavoriteWeapon )
    values ( @FirstName, @LastName, @FavoriteHandbag, @FavoriteShoe, @FavoriteWeapon );

  -- Return the new user's   UserId .
  set @UserId = Scope_Identity();
go

-- Test the SP.
declare @UserId as Int;

-- Without any favorites it ought to fail.
exec AddUser @UserId = @UserId output, @FirstName = 'William', @LastName = 'Shakespeare';

-- With any combination of favorites it ought to succeed.
exec AddUser @UserId = @UserId output, @FirstName = 'William', @LastName = 'Shakespeare',
  @FavoriteWeapon = 'pen';

exec AddUser @UserId = @UserId output, @FirstName = 'William', @LastName = 'Shakespeare',
  @FavoriteShoe = 'moccasin', @FavoriteWeapon = 'pen';

如果真正的问题是参数可能包含任意名称/值对列表，例如{ 'Topping', 'Whipped Cream' }，如果事先未知，则必须使用不同的方法.如果对的数量有限，则可以使用参数，例如@Name1, @Value1, @Name2, @Value2, ... .TVP 或 XML 参数将提供对列表的更整洁的表示.

If the Real Problem™ is that the parameters may contain an arbitrary list of name/value pairs, e.g. { 'Topping', 'Whipped Cream' }, that are unknown in advance then a different method must be used. If the number of pairs is limited then parameters could be used, e.g. @Name1, @Value1, @Name2, @Value2, ... . A TVP or XML parameter would provide a tidier representation of a list of pairs.

这篇关于创建一个参数数量不定的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！