Wrong return values using a stored procedure

[Frank Schürer]

Hi,

i am using stored procedures in my application and if i call a specific stored procedure the results are not the same as IN SSMS.
The weird thing is that only specific columns are not returned.

This is my code

Public Shared Function spMEDIENSELEKTIONPREMIUM(ByVal scope As IObjectScope,ByVal iNPUTTABLE As String,ByVal mEDIENGATTUNGEN As String,ByVal sTREUARTEN As String,ByVal eRSCHEINRHYTHMEN As String,ByVal eRSCHEINTAGE As String,ByVal eRFAHRUNGSWERTE As String,ByVal rANG As Nullable(Of SByte)) As IQueryResult
    Dim query As IQuery = scope.GetSqlQuery("[sp_MEDIENSELEKTION_PREMIUM] ?,?,?,?,?,?,?", Nothing, "VARCHAR INPUTTABLE,VARCHAR MEDIENGATTUNGEN,VARCHAR STREUARTEN,VARCHAR ERSCHEINRHYTHMEN,VARCHAR ERSCHEINTAGE,VARCHAR ERFAHRUNGSWERTE,TINYINT RANG")
    Dim res As IQueryResult = query.Execute(New Object() {iNPUTTABLE, mEDIENGATTUNGEN, sTREUARTEN, eRSCHEINRHYTHMEN, eRSCHEINTAGE, eRFAHRUNGSWERTE, rANG})
    Dim count As Integer = res.Count 'executes the query

    Return res
End Function

Selection = StoredProcedure.spMEDIENSELEKTIONPREMIUM(m_mcScope, InputTable, Chr(34) & MediaTypes & Chr(34), Chr(34) & DistributionTypes & Chr(34), AppearRhythms, AppearDays, ExperienceRatings, 1)

Dim Results As New Collection

For Each obj As Object In Selection
    str = ""
    For i As Integer = LBound(obj) To UBound(obj)
        If i <> 0 Then
            str &= obj(i) & "|"
        End If
    Next
    Results.Add(str, obj(0))
Next

The obj-Variable contains the correct number of columns returned by the stored procedure, but they are always null.

This is the result if i call the stored procedure in query analyzer

63741 576 Prima Sonntag (Aschaffenburg)

63826 4675
Stadtzeitung (Aschaffenburg)

ID      MID     MNAME
3741    576     Newspaper1

3826    4675    Newspaper2

This is what i get inside my application

schaffenburg)

ID      MID     MNAME
3741    null    null

3826    null    null

Any help would be really appreciated.
Thank you.

Public Shared FunctiospMEDIENSELEKTIONSTANDARD(ByVal scope As IObjectScope,ByVal sELEKTIONSTYP As Nullable(Of Integer),ByVal sUCHTEXT_MEDIUM As String,ByVal sUCHTEXT_GEBIET As String,ByVal mEDIENGATTUNGEN As String,ByVal sTREUARTEN As String,ByVal eRSCHEINRHYTHMEN As String,ByVal eRSCHEINWEISEN As String,ByVal eRFAHRUNGSWERTE As String,ByVal gEBIETSEBENE As String,ByVal gEBIETSFILTER As String) As IQueryResult

        Dim query As IQuery = scope.GetSqlQuery("[sp_MEDIENSELEKTION_STANDARD] ?,?,?,?,?,?,?,?,?,?", Nothing, "INTEGER SELEKTIONSTYP,VARCHAR SUCHTEXT_MEDIUM,VARCHAR SUCHTEXT_GEBIET,VARCHAR MEDIENGATTUNGEN,VARCHAR STREUARTEN,VARCHAR ERSCHEINRHYTHMEN,VARCHAR ERSCHEINWEISEN,VARCHAR ERFAHRUNGSWERTE,VARCHAR GEBIETSEBENE,VARCHAR GEBIETSFILTER")

  

Public Shared Function spMEDIENSELEKTIONPREMIUM(ByVal scope As IObjectScope,ByVal iNPUTTABLE As String,ByVal mEDIENGATTUNGEN As String,ByVal sTREUARTEN As String,ByVal eRSCHEINRHYTHMEN As String,ByVal eRSCHEINTAGE As String,ByVal eRFAHRUNGSWERTE As String,ByVal rANG As Nullable(Of SByte)) As IQueryResult
    Dim query As IQuery = scope.GetSqlQuery("[sp_MEDIENSELEKTION_PREMIUM] ?,?,?,?,?,?,?", Nothing, "VARCHAR INPUTTABLE,VARCHAR MEDIENGATTUNGEN,VARCHAR STREUARTEN,VARCHAR ERSCHEINRHYTHMEN,VARCHAR ERSCHEINTAGE,VARCHAR ERFAHRUNGSWERTE,TINYINT RANG")
    Dim res As IQueryResult = query.Execute(New Object() {iNPUTTABLE, mEDIENGATTUNGEN, sTREUARTEN, eRSCHEINRHYTHMEN, eRSCHEINTAGE, eRFAHRUNGSWERTE, rANG})
    Dim count As Integer = res.Count 'executes the query

    Return res
End Function

[PetarP]

Hello Frank Schürer,

 In order to work with out parameters you will have to write some additional code in the method that executes your stored procedure. For example if you have an out parameter that represents a category id you will have to do something similar to that:

public object[] TenMostExpensiveProducts(refint? categoryID)
        {
            SqlParameter parameterCategoryID = newSqlParameter("CategoryID", OpenAccessType.Int32, ParameterMode.InOut);
            List<SqlParameter> sqlParameters = newList<SqlParameter>()
            {
                parameterCategoryID
            };

            IDictionary<string, object> outParameters = newDictionary<string, object>();

            object[] queryResult = this.ExecuteStoredProcedure<object>("'Ten Most Expensive Products' ?", sqlParameters,outoutParameters, categoryID);
            categoryID = (int?)outParameters["CategoryID"];
            returnqueryResult;
        }

Best wishes,
Petar
the Telerik team

[Frank Schürer]

Hello Petar,

i believe i didn't express myself very well.

The problem does not have to do with specific out parameters. It's just an iqueryresult of a stored procedure.

If i just insert a select * from tablename where ... at the end of the stored procedure everything is working fine. I get all the columns that are inside of the table irregardless how many. And i do not have to write specific code for this different result.

But if i work with the following select statement at the end of the stored procedure i am experiencing the implied problem.

EXEC('select ' + @OUTPUTTABLE + '.PLZ, ' + @OUTPUTTABLE + '.MEDIUM_ID AS MID, MEDIEN.MEDIUM_NAME AS MNAME From ' + @OUTPUTTABLE + ' LEFT JOIN MEDIEN ON ' + @OUTPUTTABLE + '.MEDIUM_ID = MEDIEN.MEDIUM_ID')

I get the PLZ-column correctly but the columns MID and MNAME are both Null in every record that is returned.

Do i have anything to declare in order to get the complete results of the stored procedure?

[PetarP]

Hello Frank Schürer,

 I suggest we continue our conversation in the support ticked you opened. I will post here once the issue is resolved for the rest of the community to benefit should someone encounters the same problem.

Kind regards,
Petar
the Telerik team