Rene,

Very good and simple. Thank you.

If I have more than two cases, should I use case statement instead of if...else in similar fashion as you did below with each complete sql statement after the 'then' part of the case?

>Terry,
>
>The problem with your stored procedure will accept both integer and character parameter. To work around of it you should convert the data type so that you will not encounter a data type mismatch problem. I suggest that you need to revise your code as follows:
>
>CREATE PROCEDURE [dbo].[TEST_SelectSearch]
>@searchfor char(10),
>@searchitem char(10)
>AS
>begin
> SET NOCOUNT ON
>
> if @searchfor = 'Last Name'
> SELECT *
> FROM [dbo].[patient]
> where lastname = @searchitem
> else
> SELECT *
> FROM [dbo].[patient]
> where id = cast(@searchitem as integer)
>end
>
>
>Note that when you call your stored procedure must convert first the value of the parameter "@searchitem" to character since "@searchitem" parameter was declare as character to avoid data type mismatch problem.
>example:
>using VFP code:
>SearchFor = "ID"
>SearchItem = str(10)
>
>sqlexec("execute TEST_SelectSearch @searchfor = ?SearchFor, @searchitem = ?SearchItem")
>
>
>Rene
>
>
>
>
>The following code works fine, but I can't find a way to make the other side of the equation dynamic as well so that I can handle both character field searches and integer field searches with the same stored procedure.
>
>CREATE PROCEDURE [dbo].[TEST_SelectSearch]
>@searchfor char(10),
>@searchitem char(10)
>AS
>SET NOCOUNT ON
>SELECT *
>FROM [dbo].[patient]
>where
>
>CASE @searchfor
>when 'Last Name' then lastname
>when 'ID' then id
>end
>
>= '@searchitem'
>
>The = '@searchitem' only works for the character field search & I need:
>
>= @searchitem for the integer field search. I tried using another case statement for this part, but no luck. Would it be simplest to use one sp for the chars and one for the ints?
>
>Anyone know how to do this?
>TIA
>Terry Voss (MS Certified Partner)
>http://www.computer-consulting.com
>http://www.spokaneoutdoors.com