Get Query Results From Stored Procedure

Question

I am working on a program in VB.Net that interfaces with a database using LINQ. Right now I am attempting to grab data from the following stored procedure on the database:

CREATE PROCEDURE dbo.GetTableColumn(
    @ColName VARCHAR(MAX),
    @TblName VARCHAR(MAX),
    @Result  BIT OUT
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        BEGIN
            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 'SELECT ' + @ColName + ' FROM ' + @TblName
            EXEC (@SQL)
            SET @Result = 1
        END
    ELSE
        SET @RESULT = 0
    RETURN @Result
END

The goal of the procedure, is that I can send a column name, and table name as variables, and if the column exists within the table the data from the table is returned.

I have added the procedure to my DataLinqContext, and this is the code that references the procedure:

Public Function GetTableColumn(ByVal col As String, ByVal table As String) As AutoCompleteStringCollection
    GetTableColumn = New AutoCompleteStringCollection
    Using dbContext As New Customer_LINQDataContext
        Dim result As Boolean
        Dim query = dbContext.GetTableColumn(col, table, result)
        MessageBox.Show(query.ToString())
    End Using
End Function

Now I put in the Message Box dialogue to test the output data. However every time the method runs, the result is always 1 or 0. I can never get the actual data I am trying to query for.

So what am I doing wrong here, and how do I fix it?

EDIT: I asked over on the Database Admin Stack Exchange to see if anyone there could offer any help. I got the following answer that can hopefully help, but I am still unsure how to fix the problem.

Your code is obtaining the result of the RETURN @result at the end of the stored proc. The results you want are being passed out of the stored procedure as a ResultSet. You need to use a DataReader object to see the rows.

I'm not sure how to do that using Linq. You'll hopefully get a better answer on your StackOverflow question.

Answer 1

Stored procedures return code 0 is usually used when the sp has no error on the execution... For getting the recordset, I would void the 3rd parameter and return 1, if sp finds no table/column:

CREATE PROCEDURE dbo.GetTableColumn(
    @ColName VARCHAR(MAX),
    @TblName VARCHAR(MAX)
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        BEGIN
            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 'SELECT ' + @ColName + ' FROM ' + @TblName
            EXEC (@SQL)
        END
    ELSE
        RETURN 1
END

In your code... just use:

Dim query = dbContext.GetTableColumn(col, table)

Answer 2

So to get what I wanted I basically had to stop using Linq because I could not find any way to get that to work. I made a few modifications to the Procedure, and it now looks like so:

CREATE PROCEDURE dbo.GetTableColumn(
    @ColName VARCHAR(25),
    @TblName VARCHAR(25)
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @Col_Name))
        BEGIN
            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 'SELECT ' + @ColName + ' FROM ' + @TblName + ' WHERE ' + @ColName + ' IS NOT NULL'
            EXEC (@SQL)
        END
    ELSE
        RETURN 1
END

After that in my VB.Net program I added the following method:

Public Function ExecuteCMD(ByRef CMD As SqlCommand) As DataSet
    Dim DS As New DataSet()
    Try
        OpenDBConnection() 'Custom class to open the DB Connection'
        CMD.Connection = DB_CONNECTION 'Private member set to db connection on initialization'

        If CMD.CommandText.Contains(" ") Then
            CMD.CommandType = CommandType.Text
        Else
            CMD.CommandType = CommandType.StoredProcedure
        End If

        Dim adapter as New SqlDataAdapter(CMD)
        adapter.SelectCommand.CommandTimeout = 300

        adapter.Fill(DS)
        CloseDBConnection 'Custom class to close DB Connection'
    Catch ex As Exception
        Throw New Exception("Database Error: " & ex.Message)
    End Try
    Return DS
End Function

The credit for this method goes to Brian Webster here. It's a great bit of code that helped a lot with getting this to work. Finally I modified my GetTableColumn method to look like so:

Public Function GetTableColumn(ByVal col As String, ByVal table As String) As AutoCompleteStringCollection
    GetTableColumn = New AutoCompleteStringCollection
    Dim CMD As New SqlCommand("GetTableColumn")
    CMD.Parameters.Add("@ColName", SqlDbType.VarChar).Value = col
    CMD.Parameters.Add("@TblName", SqlDbType.VarChar).Value = table
    Dim DS As DataSet = ExecuteCMD(CMD)
    For Each DR As DataRow In DS.Tables(0).Rows
        If Not IsNothing(DR(0)) Then GetTableColumn.Add(CStr(DR(0)))
    Next
End Function

This setup gets me the results I want, and is not being a pain in my butt like LINQ has been, so I am posted this as a result in case others have the same problem as me. Ditch LINQ if it starts being frustrating, it isn't worth the time.