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