0

After working on my database a bit, I am working on a VB.Net app that accesses it using LINQ. I have the following stored procedure in 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

I have added it to my DataLINQContext and call it in the following method in my VB.Net code:

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 what I am trying to accomplish, is that the query object receives the data from the query performed by the procedure. This does not happen. Instead I only get a 1 or 0 result, based off whether or not the column exists within the table.

So obviously something is wrong somewhere. I feel it's a mess up on the part of my procedure, but I am not sure how to code it to accomplish what I want it to do.

closed as off-topic by Erik Darling, Max Vernon, Aaron Bertrand Jul 19 '18 at 14:35

If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    The procedure is OK although there's no need to return @result if you are already declaring it as an OUT parameter. You might want to limit the varchar size of the parameters to a more reasonable length and not MAX. Also your dynamic sql is returning a dynamic name/alias for the selected column, maybe you want to standardize it. – EzLo Jul 19 '18 at 14:01
  • @EzLo What do you mean by standardize? – Skitzafreak Jul 19 '18 at 14:02
  • You should also consider schema in your cose because there can be tables with the same names and structures but in different schemas, so the result of your proc may vary when executed by different users – sepupic Jul 19 '18 at 14:03
  • @Skitzafreak i mean giving a forced alias like this SET @SQL = 'SELECT ' + @ColName + ' AS SelectedColumn FROM ' + @TblName, so it always returns the same alias for any picked column and table. – EzLo Jul 19 '18 at 14:05
  • I'm voting to close this question as off-topic because it was already asked on Stack Overflow. – Aaron Bertrand Jul 19 '18 at 14:35
1

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.

Sample code for a VB.Net console application to read the results of executing a Stored Procedure:

Module Module1

    Sub Main()
        Dim cConnectionBuilder As New SqlClient.SqlConnectionStringBuilder
        Dim sConnectionString As String

        cConnectionBuilder.ApplicationName = "Test App"
        cConnectionBuilder.ConnectTimeout = 15
        cConnectionBuilder.DataSource = "<servername here>"
        cConnectionBuilder.InitialCatalog = "tempdb"
        cConnectionBuilder.IntegratedSecurity = True
        cConnectionBuilder.Pooling = False
        sConnectionString = cConnectionBuilder.ConnectionString

        Using cConnection As New SqlClient.SqlConnection(sConnectionString)
            cConnection.Open()
            'drop the existing sample_proc procedure if it exists
            Using cCommand As New SqlClient.SqlCommand("IF OBJECT_ID(N'dbo.sample_proc', N'P') IS NOT NULL DROP PROCEDURE dbo.sample_proc;", cConnection)
                cCommand.CommandType = CommandType.Text
                cCommand.ExecuteNonQuery()
            End Using
            'create the sample_proc procedure
            Using cCommand As New SqlClient.SqlCommand("CREATE PROCEDURE dbo.sample_proc (@TestID int) AS BEGIN SELECT name FROM sys.columns; RETURN 1; END", cConnection)
                cCommand.CommandType = CommandType.Text
                cCommand.ExecuteNonQuery()
            End Using
            'execute the sample_proc procedure, and print a line in the console window for each row in the ResultSet
            Using cCommand As New SqlClient.SqlCommand("dbo.sample_proc", cConnection)
                cCommand.Parameters.Add(New SqlClient.SqlParameter("@TestID", 1))
                cCommand.CommandType = CommandType.StoredProcedure
                Using dReader As SqlClient.SqlDataReader = cCommand.ExecuteReader
                    While dReader.Read
                        'get each row from the resultset
                        Dim sName As String = dReader.GetString(dReader.GetOrdinal("name"))
                        Console.WriteLine(sName)
                    End While
                    dReader.Close()
                End Using
            End Using
            cConnection.Close()
        End Using
        Console.WriteLine("Press 'Q' key to quit.")
        Dim c As ConsoleKeyInfo = Console.ReadKey
        Do Until c.Key = ConsoleKey.Q
            System.Threading.Thread.Sleep(1)
            c = Console.ReadKey
        Loop
    End Sub

End Module

Output looks like this:

account_name
account_name
addr
Press 'Q' key to quit.

Not the answer you're looking for? Browse other questions tagged or ask your own question.