Stored procedures WITH params not possible

[GoogleCodeExporter]

Hmm,I have many SP on server - I put bussines logic there and not in 
script.

While it is possible to execute some simple examples, there should be more 
options to use sp. They are standard.

Perhaps you could prepare also some options like:

1.) spParamsData - execute sp with parameteres and return recordset for 
displaying recordset/datatable
2.) spParamsInsertOrUpdate - executes sp - all logic to make actions is 
already included in sp

Or something like that - I am not a programmer so I just wrote very 
simplfied - but you will get an idea that the world without sps is like 
world without women :=)

Or - I simply don't know how to do it - if so I would be very happy with 
one example with executing sp with - lets say 2 params..one integer and one 
with string

BTW - VERY GOOD WORK

Original issue reported on code.google.com by MyChessS...@gmail.com on 20 Dec 2008 at 5:50

[GoogleCodeExporter]

Long standing issue, but I've done a little, incomplete work towards this end

in datatable.asp I altered the sub loadData() to load like this:

        If UCase(Left(sql,4)) = "EXEC" Then
            'Is likely a stored proceedure, or some other T-SQL command.
            Set data = db.getSproc(sql, empty)
            If sort <> "" Then data.Sort = "" & sort & ""
        Else
            'now execute the final sql. it is actually a subselect
            'so all columns are passed through and can be accessed by filters, etc.
            sqlFinal = "SELECT * FROM (" & sql & ") datatable" & lib.iif(sort <> "", " ORDER BY " & db.SqlSafe(sort), "")
            set data = db.getUnlockedRS(sqlFinal, empty)
        End If

In database.asp I added this function:

    '******************************************************************************************************************
    '' @DESCRIPTION:    Gets an UNLOCKED recordset from the currently opened database, using a stored procedure
    '' @PARAM:          sql [string]: The stored procedure to execute
    '' @PARAM:          params [array], [string]: any paramaters that need to be passed to the stored procedure
    '' @RETURN:         [recordset] recordset with data from the stored procedure
    '******************************************************************************************************************
    public function getSproc(byVal sql, params)
        sql = parametrizeSQL(sql, params, "Database.getSproc()")
        debug(sql)
        on error resume next
        set getSproc = server.createObject("ADODB.RecordSet")
        getSproc.cursorLocation = adUseClient '3
        getSproc.cursorType = 3
        getSproc.open sql, connection
        if err <> 0 then
            errdesc = err.description
            on error goto 0
            lib.throwError(array(101, "Database.getSproc()", "Could not execute '" & sql & "'. Reason: " & errdesc, sql))
        end if
        on error goto 0
        p_numberOfDBAccess = p_numberOfDBAccess + 1
    end function

And I've been executing like this:

dt.sql = "EXEC sprSkillsAssessmentRangedData @StartDateTime='" & cStr(strFrom) 
& "', @EndDateTime='" & cStr(strTo) & "';"

Not the most elegant, and not quite as extensive, but it's a start. I have 
noted that the search doesn't work with just those small changes - still 
working on that. I'll try to draw up a formal submission once I feel better 
about having a complete solution. Mostly I'm leaving this so I won't forget 
what I've done. :)

Original comment by mbussel...@gmail.com on 4 May 2012 at 5:48

[GoogleCodeExporter]

Added rudimentary support for Stored Procedures. Searching and sorting will not 
work with data tables created with the new function (see #6)

Original comment by mbussel...@gmail.com on 18 Jun 2013 at 2:14

• Changed state: Started

[GoogleCodeExporter]

Original comment by mbussel...@gmail.com on 25 Jun 2013 at 9:44

• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

Issue 9 has been merged into this issue.

Original comment by mbussel...@gmail.com on 25 Jun 2013 at 9:46