Why is my stored procedure not running correctly?

I have an asp.net 4.0 app hitting a sql 2008 db. I have a very basic stored proc designed to return records. It works when I execute it in the .net ide server explorer. But when I step through the .net code, even though 开发者_运维技巧the variable is being passed in the way I like (and have done many imes before), nothing is being returned. Here is the stored proc...

    ALTER PROCEDURE get_cases_by_search_criteria
    @vin as varchar(50) = null

AS
declare @sqlstr varchar(5000)

set @sqlstr = 'SELECT 
                [Case].CaseID, 
                [Case].VIN, 
                [Case].Make, 
                [Case].Model, 
                Contact.FirstName, 
                Contact.LastName, 
                FROM  [Case] INNER JOIN
                Contact ON [Case].CaseID = Contact.CaseID 
                Where Contact.ContactTypeID = 1 '

                if  @vin is not null and @vin <> ''
                BEGIN
                    set @sqlstr = @sqlstr + ' and ' + ('[Case].VIN = ''' + convert(varchar,@vin) + '''') 
                END

exec(@sqlstr)

And here is how I pass in the values...

        'non-specific parameter
        param1 = oFactory.CreateParameter()
        param1.ParameterName = "@vin"
        param1.DbType = DbType.String
        param1.Value = oSearchCriteria.VIN
        oCmd.Parameters.Add(param1)

        oCmd.CommandType = CommandType.StoredProcedure
        oCmd.CommandText = "get_cases_by_search_criteria"

        Using (oConnection)
            oConnection.Open()
            oReader = oCmd.ExecuteReader()

            While oReader.Read

---

What happens when @vin equals '; DROP DATABASE UserDB; -- ?

This might not be the problem you were looking to solve, but the way you're using dynamic SQL makes you vulnerable to a SQL Injection Attack.

Instead of using exec(@sql) string, you should use sp_executesql, and parameterize @vin

DECLARE @sqlstr varchar(5000)
DECLARE @ParmDefinition NVARCHAR(500)

SET @sqlstr  = 'SELECT 
                [Case].CaseID, 
                [Case].VIN, 
                [Case].Make, 
                [Case].Model, 
                Contact.FirstName, 
                Contact.LastName, 
                FROM  [Case] INNER JOIN
                Contact ON [Case].CaseID = Contact.CaseID 
                Where Contact.ContactTypeID = 1 '

IF  @vin is not null and @vin <> ''
BEGIN
    SET @sqlstr = @sqlstr + ' and [Case].VIN = @vinparm'
END

SET @ParmDefinition = N'@vinparm varchar(50)'

EXECUTE sp_executesql @sqlstr, @ParmDefinition, @vinparm = @vin