return from sql without sp

[Neilf]

What can be returned from a sql execute command without a sp? I want to report an error if it occurs - ie if the transaction is rolled back. I've only had any success with the RecordsAffected property on the Execute method. My sql statement consists of multiple inserts, this only seems to return the result from the 1st insert. Can an error code be returned or can a variable from within the statement be returned.

I don't think i can use a sp as the sql statement is built dynamically, or is it possible to build and then delete a sp from within asp?

Thanks in advance!

 

[cmmrfrds]

You can create dynamic SQL in a stored procedure(SP). It would be a question of sending enough information to the stored procedure to determine how to format the dynamic sql. For example, you could send the information in parameters to the SP and then build the inserts in the SP, or you could send partially constructed statements as parameters and then finish the construction in the SP. Actually it would make your ASP page cleaner to off load the sql construction to the SP.

Basic syntax.
declare @sql varchar(2000)
'logic to create sql string
set @sql = 'insert..... whatever else. etc...'
exec (@sql)

 

[Neilf]

Thanks for that - i was thinking along those lines - its going to be messy though as my sql string can be of infinite length (well i guess i can put some restriction on it - its for a dynamic survey result insert, the surveys can be a bit lengthy - the string i just tested was almost 22000 chars)- the max variable length is 8000 but it looks like i can split the string to several variables.

 

[cmmrfrds]

Another way to do it and it is more flexible (open ended on the # or inserts) is to do it with an ADO recordset. The high level overview is to create an ADO recordset with client side cursor, then do the inserts into the recordset. After the recordset is complete then establish a connection to the server and do a batch update of your table. If you want to explore this route, I recommend a book by Rob Macdonald called "Serious ADO". Actually, there are many features in ADO that are valuable for ASP programming and the book was an excellent investment.

 

[Neilf]

Thanks i'll check it out!