This is a migrated thread and some comments may be shown as answers.

[Solved] Wrong return values using a stored procedure

3 Answers 166 Views

OQL (OQL specific questions)

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Frank Schürer asked on 27 Oct 2010, 04:28 PM

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")

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

3 Answers, 1 is accepted

answered on 01 Nov 2010, 06:57 PM

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

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

answered on 02 Nov 2010, 11:38 AM

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?