>>How do you deal with passing in differing parameters into the stored procedures within SQL server? specifically the update stored procedures.<

No problem, Bernard. Below is an example of a Stored Proc. Depending on the key passed to it, it will either do an Insert or an Update. Notice the use of the ISNULL function which, in effect, deals with parameters that have not been passed (since they are initialized to NULL).

Does this help? Does it make sense now?

CREATE PROCEDURE bsp_MyTablePut
	@MyTablekey     bigint      = NULL OUTPUT,
	@MyFirstColumn	int         = NULL,
	@MySecondColumn	varchar(64) = NULL,
	@MyThirdColumn	char(2)     = NULL
AS
	IF (@MyTablekey = 0 OR @MyTablekey IS NULL)
	BEGIN
		-- Insert Values into the MyTable table
		INSERT MyTable
			(MyFirstColumn,
			 MySecondColumn,
			 MyThirdColumn)
		SELECT @MyFirstColumn,
			@MySecondColumn,
			@MyThirdColumn

		SELECT @MyTablekey = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		-- Update the MyTable table
		UPDATE MyTable
		SET MyFirstColumn = ISNULL(@MyFirstColumn, MyFirstColumn),
		    MySecondColumn = ISNULL(@MySecondColumn, MySecondColumn),
		    MyThirdColumn = ISNULL(@MyThirdColumn, MyThirdColumn),
		WHERE MyTablekey = @MyTablekey
	END

~~Bonnie



>Hi Bonnie, thanks for the reply, one small detail I'm not quite clear on.
>
>How do you deal with passing in differing parameters into the stored procedures within SQL server? specifically the update stored procedures.
>
>for example, if we have a table with 5 fields, named field1 to field5, and imagine we need to update field1 & field2 in one situation and field3 & field5 in another.
>
>Do you create and call a seperate stored procedure for each update, eg have a stored procedure that expects to receive parameters for field1 and field2 and only updates those fields and have another to update fields3 & field5.
>
>Or do you have one 'smart' stored procedure that recieves varying parameters and determines what is has been passed and works out what fields to update?
>
>The last option would seem to be the way to go, but I don't have a clue about how to structure the SP to cope with this,....
>
>Any pointers you could give me would be gratefully accepted...
>
>Thanks, Bernard....