SQL Select Print Out Results of Stored Procedure

SQL select print out results of stored procedure

You can try setting the values in output parameter

create procedure sp_test_print_out
@printMessages varchar(max) output
as
begin
set @printMessages='Test'
Print 'Test';

set @printMessages= @printMessages + CHAR(10)
set @printMessages= @printMessages + 'Test 1'
print 'Test 1';
end
go

create procedure sp_test_print_out_to_select
as 
begin
declare @printOut varchar(max)
exec sp_test_print_out @printOut output -- can be achieved using output parameter ?
select @printOut
end

go

exec sp_test_print_out_to_select

Printing result of a stored procedure in T-SQL

Are you attempting to do something like this:

CREATE PROCEDURE spTEST2
    @spSR INT
AS
    ...
GO

 DECLARE @RESULT MONEY
 EXEC @RESULT = spTEST2 1
 PRINT @RESULT

After reading your edited question I think I know what you need to do. What you need to do is first create all your stored procedures.

CREATE spTEST1 GO //In an ideal world you would have already deployed these to you test environment.
CREATE spTEST2 GO 
CREATE spTEST3 GO

...Next...

Declare three variables like

DECLARE 
    @Result1 MONEY, 
    @Result2 MONEY,
    @Result3 MONEY 

Then do your calls...

EXEC @Result1 = spTEST1 1
EXEC @Result2 = spTEST1 2
EXEC @Result3 = spTEST1 3

Or Try this...

EXECUTE spTEST1 1, @SR_rev= @Result1 OUTPUT;
EXECUTE spTEST2 2, @SR_rev= @Result2 OUTPUT;
EXECUTE spTEST3 3, @SR_rev= @Result3 OUTPUT;   

...finally PRINT...

PRINT @Result1 
PRINT @Result2 
PRINT @Result3

...and that should work.

SQL Server PRINT SELECT (Print a select query result)?

You know, there might be an easier way but the first thing that pops to mind is:

Declare @SumVal int;
Select @SumVal=Sum(Amount) From Expense;
Print @SumVal;

You can, of course, print any number of fields from the table in this way. Of course, if you want to print all of the results from a query that returns multiple rows, you'd just direct your output appropriately (e.g. to Text).

Get Query Results From Stored Procedure

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.

Print selected values

If you want to get the table name as an argument, you should use exec.

create proc sp_getValue
 @tableName varchar(30)
as
  exec('select distinct col1 from '+ quotename(@tableName) + ' where col2 is null'); 

To turn this table into a string you can output the result into some temp table, and traverse that using a cursor. Something like this:

DECLARE @value  NVARCHAR(30)
DECLARE @result NVARCHAR(100) = '';
CREATE TABLE #temp
(
    col1 NVARCHAR(30)
)

EXEC ('insert into #temp select col1 from ' + @tableName +' where col2 is null')

DECLARE myCur CURSOR FOR SELECT *
                         FROM #temp;
open myCur;
FETCH next from myCur  INTO @value;

WHILE @@FETCH_STATUS = 0
    BEGIN
        set @result = @result + @value + ',';
        FETCH next from myCur INTO @value;
    END

PRINT @result

close myCur;
DEALLOCATE myCur;

How to print select results from within a stored procedure

Wouldn't it be possible to SELECT what you need? Sorry if I misunderstood the question. Selected variables/tables will be returned as results

Stored Procedure PRINT each row value

I think the best way would be to select the output you want to show the user at the end of the procedure:

CREATE PROC dbo.test
AS
BEGIN
    --do stuff
    INSERT INTO foo (bar)
    SELECT name
    FROM persons

    --select what to show user
    SELECT name
    FROM persons
END

Stored procedure and print output

Instead of a procedure, use a view. However, there are other things to fix:

• Never use commas in the FROM clause. Always use explicit JOIN syntax.
• Qualifying column names is good, but you need to use the table alias rather than the column name

The view looks like:

CREATE VIEW vw_top10ProfitableProducts as
    SELECT Top 10 p.productID, p.description, ci.quantityOrdered,
           (i.sellingPrice - i.costPrice) as Difference 
    FROM Product p JOIN
         Supplier s
         ON p.ProductID = s.productID JOIN
         SupplierOrder so
         ON s.supplierID = so.supplierID JOIN
         Inventory in
         ON so.supOrderID = in.supOrderID JOIN
         CustomerInventory cin
         ON in.barcodeNo = cin.barcodeNo
    ORDER BY difference Desc;

You can then access it just like a regular table:

select *
from vw_top10ProfitableProducts;

This is much easier than using stored procedures. In any case, your stored procedure could never work, because you want to return ten rows, but you have only four output parameters.

---

Related Topics