Dynamic Stored Procedures, ASP, ADO

<i><b>Originally posted by : tantulus (tantulus@my-Deja.com)</b></i><br />Hello All,<br /><br /> Well I have been using this Stored Procedure which essentially <br />calls EXECUTE(sqlstring). The sqlstring so far has been built in<br />asp doing the normal tricks to add sorting and joining, etc. So I<br />was presented with a challenge since this Stored Procedure doesn't<br />compile perhaps there would be a way to optimize it.<br /><br />First I looked at sp_executesql. Then I thought perhaps I could <br />use if...else if AND CASE...WHEN and pass a few integer values<br />in and the Stored Procedure could execute the different types of<br />queries based on what values where passed in. This is going to <br />make a lengthy Stored Procedure although it should be compiled which<br />is why I do it.<br /><br />Anyway I came up with my first draft. It is very simple just passes<br />in an integer that decides which table to use. I don't understand when you are suppose to use GO. I get a syntax error when I try to put that in but tutorial on Stored Procedures use this. The code works in Analyzer.<br /> I have pasted the code below and the error I get is:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e18' <br />The rowset was built over a live data feed and cannot be restarted <br /><br />/ut/funds/msb-perf-cont-min.asp, line 58 <br /><br />And the STORED PROCEDURE:<br /><br />CREATE PROCEDURE fund_perf_new @var1 integer AS<br /><br />IF @var1 = 0<br /> BEGIN<br /> SELECT StandingData2.DS_CODE, <br /> StandingData1.TN_ID, <br /> StandingData2.FUND, <br /> StandingData2.UNIT_TYPE, <br /> StandingData2._1YR_CHART, <br /> StandingData1.GROUP_, <br /> StandingData1.CAT_STATUS, <br /> Daily.TYPE, <br /> Daily.CURRENCY, <br /> Groups.GROUP_FILE, <br /> Daily.PRICE_BID, <br /> Daily.PRICE_MID, <br /> Daily.PRICE_ASK, <br /> Daily.YIELD, <br /> Daily.PR_TR1Y, <br /> Daily.PR_TR3Y, <br /> Daily.PR_TR5Y <br /> FROM ListCombUnits, FundDetails, StandingData1, StandingData2, Daily, Groups<br /> WHERE ListCombUnits.DS_CODE = StandingData2.DS_CODE AND <br /> StandingData1.TN_ID = StandingData2.TN_ID AND<br /> StandingData2.DS_CODE = Daily.DS_CODE AND <br /> StandingData1.GROUP_ = Groups.GROUP_ AND <br /> StandingData1.TN_ID = FundDetails.TN_ID<br /> ORDER BY StandingData2.FUND<br /> END <br />ELSE IF @var1 = 1<br /> BEGIN<br /> SELECT StandingData2.DS_CODE, <br /> StandingData1.TN_ID, <br /> StandingData2.FUND, <br /> StandingData2.UNIT_TYPE, <br /> StandingData2._1YR_CHART, <br /> StandingData1.GROUP_, <br /> StandingData1.CAT_STATUS, <br /> Daily.TYPE, <br /> Daily.CURRENCY, <br /> Groups.GROUP_FILE, <br /> Daily.PRICE_BID, <br /> Daily.PRICE_MID, <br /> Daily.PRICE_ASK, <br /> Daily.YIELD, <br /> Daily.PR_TR1Y, <br /> Daily.PR_TR3Y, <br /> Daily.PR_TR5Y <br /> FROM ListUnits, FundDetails, StandingData1, StandingData2, Daily, Groups<br /> WHERE ListUnits.DS_CODE = StandingData2.DS_CODE AND <br /> StandingData1.TN_ID = StandingData2.TN_ID AND<br /> StandingData2.DS_CODE = Daily.DS_CODE AND <br /> StandingData1.GROUP_ = Groups.GROUP_ AND <br /> StandingData1.TN_ID = FundDetails.TN_ID<br /> ORDER BY StandingData2.FUND<br /> END<br /><br /><br />AND the ASP CODE<br /><br />Set dbConn1 = Server.CreateObject("ADODB.Connection")<br />dbConn1.Open "FUNDS"<br /><br />Set rsSet1 = CreateObject("ADODB.RecordSet")<br />With rsSet1<br /> .PageSize = cstPageSize<br /> .LockType = adLockReadOnly<br /> .CursorType = adOpenStatic<br /> .CursorLocation = adUseClient<br />End With<br /><br />Set cmdGetUTFunds = Server.CreateObject("ADODB.Command")<br />With cmdGetUTFunds<br /> .ActiveConnection = dbConn1<br /> .Parameters.Append .CreateParameter ("@var1", adInteger, adParamInput, , 0)<br /> .CommandText = "fund_perf_new"<br /> .CommandType = adCmdStoredProc<br /> Set rsSet1 = .Execute( lngRecs, , adCmdStoredProc)<br />End With<br />set cmdGetUTFunds = Nothing<br /><br /><br /><br />Maximum Thanks in Advance,<br />Tantulus<br /><br />

<i><b>Originally posted by : tantulus (tantulus@my-deja.com)</b></i><br /><br />Well I did it using temporary tables. Also help came via the USENET about the CASE...WHEN SQL command like this<br /><br />SELECT *<br /> FROM Foobar<br />WHERE (CASE :flag<br /> WHEN 1 THEN<br /> CASE WHEN <predicate #1> THEN 1 ELSE 0<br /> WHEN 2 THEN<br /> CASE WHEN <predicate #2> THEN 1 ELSE 0<br /> WHEN 3 THEN<br /> CASE WHEN <predicate #3> THEN 1 ELSE 0<br /> ...<br /> ELSE 0 END) = 1;<br /><br />------------<br />tantulus at 7/19/2000 12:16:10 PM<br /><br />Hello All,<br /><br /> Well I have been using this Stored Procedure which essentially <br />calls EXECUTE(sqlstring). The sqlstring so far has been built in<br />asp doing the normal tricks to add sorting and joining, etc. So I<br />was presented with a challenge since this Stored Procedure doesn't<br />compile perhaps there would be a way to optimize it.<br /><br />First I looked at sp_executesql. Then I thought perhaps I could <br />use if...else if AND CASE...WHEN and pass a few integer values<br />in and the Stored Procedure could execute the different types of<br />queries based on what values where passed in. This is going to <br />make a lengthy Stored Procedure although it should be compiled which<br />is why I do it.<br /><br />Anyway I came up with my first draft. It is very simple just passes<br />in an integer that decides which table to use. I don't understand when you are suppose to use GO. I get a syntax error when I try to put that in but tutorial on Stored Procedures use this. The code works in Analyzer.<br /> I have pasted the code below and the error I get is:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e18' <br />The rowset was built over a live data feed and cannot be restarted <br /><br />/ut/funds/msb-perf-cont-min.asp, line 58 <br /><br />And the STORED PROCEDURE:<br /><br />CREATE PROCEDURE fund_perf_new @var1 integer AS<br /><br />IF @var1 = 0<br /> BEGIN<br /> SELECT StandingData2.DS_CODE, <br /> StandingData1.TN_ID, <br /> StandingData2.FUND, <br /> StandingData2.UNIT_TYPE, <br /> StandingData2._1YR_CHART, <br /> StandingData1.GROUP_, <br /> StandingData1.CAT_STATUS, <br /> Daily.TYPE, <br /> Daily.CURRENCY, <br /> Groups.GROUP_FILE, <br /> Daily.PRICE_BID, <br /> Daily.PRICE_MID, <br /> Daily.PRICE_ASK, <br /> Daily.YIELD, <br /> Daily.PR_TR1Y, <br /> Daily.PR_TR3Y, <br /> Daily.PR_TR5Y <br /> FROM ListCombUnits, FundDetails, StandingData1, StandingData2, Daily, Groups<br /> WHERE ListCombUnits.DS_CODE = StandingData2.DS_CODE AND <br /> StandingData1.TN_ID = StandingData2.TN_ID AND<br /> StandingData2.DS_CODE = Daily.DS_CODE AND <br /> StandingData1.GROUP_ = Groups.GROUP_ AND <br /> StandingData1.TN_ID = FundDetails.TN_ID<br /> ORDER BY StandingData2.FUND<br /> END <br />ELSE IF @var1 = 1<br /> BEGIN<br /> SELECT StandingData2.DS_CODE, <br /> StandingData1.TN_ID, <br /> StandingData2.FUND, <br /> StandingData2.UNIT_TYPE, <br /> StandingData2._1YR_CHART, <br /> StandingData1.GROUP_, <br /> StandingData1.CAT_STATUS, <br /> Daily.TYPE, <br /> Daily.CURRENCY, <br /> Groups.GROUP_FILE, <br /> Daily.PRICE_BID, <br /> Daily.PRICE_MID, <br /> Daily.PRICE_ASK, <br /> Daily.YIELD, <br /> Daily.PR_TR1Y, <br /> Daily.PR_TR3Y, <br /> Daily.PR_TR5Y <br /> FROM ListUnits, FundDetails, StandingData1, StandingData2, Daily, Groups<br /> WHERE ListUnits.DS_CODE = StandingData2.DS_CODE AND <br /> StandingData1.TN_ID = StandingData2.TN_ID AND<br /> StandingData2.DS_CODE = Daily.DS_CODE AND <br /> StandingData1.GROUP_ = Groups.GROUP_ AND <br /> StandingData1.TN_ID = FundDetails.TN_ID<br /> ORDER BY StandingData2.FUND<br /> END<br /><br /><br />AND the ASP CODE<br /><br />Set dbConn1 = Server.CreateObject("ADODB.Connection")<br />dbConn1.Open "FUNDS"<br /><br />Set rsSet1 = CreateObject("ADODB.RecordSet")<br />With rsSet1<br /> .PageSize = cstPageSize<br /> .LockType = adLockReadOnly<br /> .CursorType = adOpenStatic<br /> .CursorLocation = adUseClient<br />End With<br /><br />Set cmdGetUTFunds = Server.CreateObject("ADODB.Command")<br />With cmdGetUTFunds<br /> .ActiveConnection = dbConn1<br /> .Parameters.Append .CreateParameter ("@var1", adInteger, adParamInput, , 0)<br /> .CommandText = "fund_perf_new"<br /> .CommandType = adCmdStoredProc<br /> Set rsSet1 = .Execute( lngRecs, , adCmdStoredProc)<br />End With<br />set cmdGetUTFunds = Nothing<br /><br /><br /><br />Maximum Thanks in Advance,<br />Tantulus<br /><br />