Help With Sp_executesql And Return Parameter

I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.





While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.





So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:





Public Function totalrecordsbysql(list as arraylist) as integer


dim RetVal as new integer


dim querystring as string





Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))


Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)


cmd.commandtype = commandtype.storedprocedure





dim mydr as SqlDataReader





cmd.Parameters.add("@TotalRecords",SqlDbType.int).direction=ParameterDirection.Output


cmd.Parameters.add("@age",sqldbtype.int).value = 18





cn.Open()





try


mydr=cmd.executereader()


catch e as sqlexception


dim err as sqlerror


dim strErrorString as string





for each err in e.Errors


strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message


trace.write("sqlexception",strErrorString)


Next





finally





RetVal = cmd.parameters("@TotalRecords").value





end try








Return RetVal


cn.close()


End Function





Now here is the stored procedure:





CREATE PROCEDURE SimpleDynProfileCount





@age int,


@TotalRecords int output





AS





Declare @sql nvarchar(4000),


@paramlist nvarchar(4000)





select @sql = 'select @xTotalRecords = count(*) from profile where 1 = 1 '





// RAISERROR(@sql, 16, 1)





IF @age > 0


Select @sql = @sql + ' AND age > @xage '





Select @paramlist = '@xage int, @xTotalRecords int output'





Execute sp_executesql @sql,@paramlist,@age,@xTotalRecords = @TotalRecords output





select @TotalRecords


GO





Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.


The data is the database should return 11 records, based on the criteria of age > 11