You know, Kevin, you really should have started another thread. This one's kinda wandered a bit.

But, if you don't mind me jumping in here, there's a coupla different ways you can handle this type of situation in a Stored Proc. Mostly, you don't want to do something totally generic (it's easier that way). IOW, you might have the need to query a Customer table several different ways, so from the get-go you know you'll be doing a SELECT from customer.

You can have a Stored Proc that simply accepts the Where clause as a parameter and you set that where clause in code depending on what the user has entered in the Query form. This is probably the easiest, but I don't know if it's the best.

Another option is to have a Stored Proc that accepts a limited number of parameters, set them equal to NULL in the SP so you can tell which ones were not passed when the SP is called, create a table variable and select into that. Here's an example of what I mean by this:

CREATE PROCEDURE GetCustomerListBySearchCriteria
	@lastname	varchar(40) = NULL,
	@firstname	varchar(40) = NULL,
	@streetnumber	varchar(40) = NULL,
	@Street 	varchar(40) = NULL,
	@city		varchar(40) = NULL,
	@state		char(2)     = NULL
AS
	DECLARE @Customer TABLE(lastname varchar(40), etc.)

	DECLARE @RowCount bigint
	SET @RowCount = -1

	IF @lastname IS NOT NULL AND @lastname <> ""
	BEGIN
		INSERT INTO @Customer (lastname, etc.)
		SELECT 	lastname, etc.
		  FROM	customer
		 WHERE	lastname = @lastname

		SET @RowCount = @@ROWCOUNT
	END

	IF @firstname IS NOT NULL AND @firstname <> ""
	  IF @RowCount > -1
		DELETE FROM @Customer 
		 WHERE firstname <> @firstname OR firstname IS NULL
	  ELSE
	  BEGIN
		INSERT INTO @Customer (lastname, etc.)
		SELECT 	lastname, etc.
		  FROM	customer
		 WHERE	firstname = @firstname

		SET @RowCount = @@ROWCOUNT
	  END

	IF @Street IS NOT NULL AND @Street <> ""
	  IF @RowCount > -1
		DELETE FROM @Customer 
		 WHERE street <> @Street OR street IS NULL
	  ELSE
	  BEGIN
		INSERT INTO @Customer (lastname, etc.)
		SELECT 	lastname, etc.
		  FROM	customer
		 WHERE	street = @Street

		SET @RowCount = @@ROWCOUNT
	  END

etc.etc.etc.

	SELECT * FROM @Customer

This example is too simplified to see much good in it, but if you had more complicated SELECTs with JOINs and such, this works pretty good and is much easier to do than passing the WHERE clause.

Anyway, that's just my 2 cents.

~~Bonnie




>Kevin,
>
>Something else I'v been thinking about that I wanted to get your
>thoughts on;
>
>I am going to use Stored Procs to get data, and in mose cases, with a little
>forethought, I can create SP's for most queries I will need.
>
>But there is always the dynamic query to think about. I have a few apps
>where the user decides the WHERE clause based in selections in a Query
>screen.
>
>Is there some way to handle this with Stored Procedures?
>
>Thanks!
>
>
>
>>Hey, Kevin,
>>
>>To answer your last question, assuming you're getting your result set back as a DataSet, you can do "MyDataSet.GetXml()" to convert the dataset to an XML representation.
>>
>>You're on the right track for your stored procedure example. Obviously, some tables might have multiple stored procs.
>>
>>Stored procs are generally the way to go. They allow you to develop and test result sets that are client-agnostic.
>>
>>Mine is set up to use a collection of parameters, since one stored proc might have one string parm, a second might have 3 parms of different data types, etc. You're going about it the right way, and you're establishing some good foundations. And even if you don't immediately intend to implement something (web services) for a distributed architecture, the better you structure things now, the easier it will be to fit them in down the road.
>>
>>Kevin