Dynamic SQL

Results 1 to 2 of 2

Thread: Dynamic SQL

  1. #1
    Neen Guest

    Default Dynamic SQL

    Hi, <BR><BR>I have just upsized an ASP application from Access to SQL Server, and in the process I have to create stored procedures to replace the access queries. The query that does the main search in the application is dynamically built SQL, built in the ASP from values entered into a form. There are too many options to easily create a stored procedure, so instead I am passing the dynamically built SQL string to the database as a parameter to a stored procedure that consists of this:<BR><BR><BR>CREATE PROCEDURE upRunSql<BR>@sSql nVarChar(4000)<BR>AS<BR>execute @sSql<BR><BR>This works (sortof). I get the data returned, however the returned RecordCount is -1. (My cursor type is adOpenStatic) I can&#039t use GetRows or AbsoultePage either...Can anyone tell my why this won&#039t work? Or tell me of a better way to do this?<BR><BR>Thanks very much in advance<BR><BR>Neen<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Dynamic SQL

    You could use a parameter to return the recordcount. Like this:<BR><BR>CREATE PROCEDURE upRunSql<BR>(<BR>@sSql nVarChar(4000),<BR>@RCount integer output<BR>)<BR>AS<BR>Begin<BR>execute @sSql<BR>select @RCount = @@ROWCOUNT<BR><BR>Then in your ado just reference the second parameter&#039s value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts