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 > 11Your code works fine for me in Query Analyzer -- with one exception. I needed to change the double forward slashes (//) to double dashes (--) in front of the RAISERROR.

You might also place a SET NOCOUNT ON at the top of your stored procedure. This will suppress the information "xx items selected" messages and will avoid having them returned inadvertently as a resultset.

Terri|||That's what is so strange: I don't receive error messages, just the wrong answer (zero). The double dashes are just artistic license: I didn't remember the comment tag. I just eliminate the line, altogether, in production.|||Try adding SET NOCOUNT ON after your AS at the top of the stored procedure.

Terri|||I have added the statement, but the results are the same. Works fine with the RAISERROR statement, but returns zero without the statement.

I don't want to just leave the RAISERROR statement active. It will come back to haunt me later.

rod|||I have resolved the syntax.

I made a mistake in the vb.net when I used a datareader to retrieve the output value!

Instead of:

mydr=cmd.executereader()

the syntax should be:

cmd.executeNonQuery()

Using this syntax I removed from the Stored Procedure, the RAISERROR statement and the last SELECT @.totalrecords.

After doing all that, everything worked as expected.

-rod

Help with reuse methology?

I find that many stored procedures are necessary in nearly all databases I
use. Creating dynamic (one that takes a string and executes the sql in it)
stored procedures is expensive performance wise. However, they do allow you
to pass in strings of sql and execute it. I find in general that SPs are
created and can only be used for one specific thing - registration form
input, account info update, etc. For example, I can't take the account
update SP and use it for the registration input SP.
Is there some general or fundamental guide that should be followed to lower
the overall number of stored procedures in a database?
Thanks,
BrettBrett wrote:
> I find that many stored procedures are necessary in nearly all
> databases I use. Creating dynamic (one that takes a string and
> executes the sql in it) stored procedures is expensive performance
> wise. However, they do allow you to pass in strings of sql and
> execute it. I find in general that SPs are created and can only be
> used for one specific thing - registration form input, account info
> update, etc. For example, I can't take the account update SP and use
> it for the registration input SP.
> Is there some general or fundamental guide that should be followed to
> lower the overall number of stored procedures in a database?
> Thanks,
> Brett
Not really. You need what you need. Dynamic SQL is not the way to go.
It's really no different than executing the SQL statement directly from
the client and exposes your database to SQL injection and performance
issues.
Writing stored procedures is just a part of the application development
process. You can leverage things like functions or utility-type stored
procs to simplify repetitive code, but not much you can do to lower the
number of procedures you need.
David Gugick
Imceda Software
www.imceda.com