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.

have you tried to execute this procedure in sql server without linq? — Marian Nasry
– Marian Nasry, Commented Jul 19, 2018 at 13:09
try finding trace from SQL profiler for execution of this Stored proc to see if parameters are coming as expected. — Pranav Singh
– Pranav Singh, Commented Jul 19, 2018 at 13:14
just like Sql Server Management Studio, you have tool Profiler installed with SQL Server. Start Profiler & start trace & find stored procedure name for execution result of sp — Pranav Singh
– Pranav Singh, Commented Jul 19, 2018 at 13:23

AMG · Accepted Answer · 2018-07-19 16:03:00Z

0

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)

answered Jul 19, 2018 at 16:03

AMG

101

Sign up to request clarification or add additional context in comments.

2 Comments

Skitzafreak Over a year ago

Thank you, but I am still having the problem. I need the procedure to give me the data in the specific table column. However, even with making this change to the procedure and code, I am only getting a value of 0 from running the procedure.

AMG Over a year ago

You said in SQL when running the SP. It works fine. If so... the problem seems to be the objects you are using in your code... try with this example to retrive the sp.... (C# and VB) msdn.microsoft.com/en-us/library/…

Skitzafreak · Accepted Answer · 2018-07-19 17:36:37Z

0

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.

answered Jul 19, 2018 at 17:36

Skitzafreak

1,9378 gold badges35 silver badges57 bronze badges

4 Comments

Mary Over a year ago

I don't get this If CMD.CommandText.Contains(" ") Then. You are saying if the .CommandText has a space in it. How can that determine whether the command is type .Stored Procedure or type .Text?

Skitzafreak Over a year ago

@Mary If I am writing a SELECT command, the CommandText would look like 'SELECT col FROM table'. Detecting the space lets you know if it's a normal query as opposed to just calling a procedure

Mary Over a year ago

There is a great answer at stackoverflow.com/questions/51372427/… from Dan Guzman, In the stored procedure he suggest the data type sysname for columns and tables. Then in the VB code the equivalent is SqlDbType is nvarchar, 128

Aaron Bertrand Over a year ago

Does Dim CMD As New SqlCommand("EXEC dbo.GetTableColumn") not work? Or Dim CMD As New SqlCommand("[Procedure with spaces]")? I don't know if I would rely on a lack of a space to determine this must be a stored procedure call.

Collectives™ on Stack Overflow

Get Query Results From Stored Procedure

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related