Hello,

I have Windows Vista, and I am running IE 7.0.

In Microsoft SQL Server 2005, I created a stored procedure to be called by Classic ASP 3.0.

I am having an issue;

Stored Procedure:

-- Dynamic SQL server execution

CREATEprocedure [dbo].[usp_Product_Search_Result]
(
@product_ID int= 0,
@product_name varchar(100)=''
)
AS
Declare @strSQL varchar(5000)
Declare @strWhere varchar(5000)
SELECT @strSQL = "SELECT * FROM tblProducts WHERE 1 = 1"

select @strWhere = ""

-- Product_ID
if @product_id > 0
begin
Select @strWhere = @strWhere + " AND Product_ID = " +convert(varchar(20), @product_id)
end

-- Product Name
IFLen(@product_name)<> 0
begin
Select @strWhere = @strWhere + " AND ltrim(rtrim(product_name)) = '" + @product_name + "'"
end
select @strSQL = @strSQL + @strWhere
Print(@strSQL)
Execute(@strSQL)


ASP:
call commandcon(con, "dbo.usp_Product_Search_Result")

iflen(trim(request(" product_id" ))) > 0 then
cmd.Parameters("@product_id") = cint(trim(request("product_id" )))
endif

Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.Open cmd

==============================
It fails. If I don't use Dynamic SQL, then it is fine.

Thank you for your help.

Mustafa

__________________
MCinar

Love all the creatures because of the creator.