How to do this in stored procedure?

Question

Here's my stored procedure

CREATE PROCEDURE Test_SP
        @from int,
        @to int,
        @year int,
        @office varchar(MAX),
        @fund varchar(MAX)
AS             
SELECT        
        AccntTbl.Id,
         AccntTbl.accnt,
          ISNULL(SupplyTbl.Supply, 0) AS Supply,
           AccntTbl.office,
            AccntTbl.exp,                 
        AccntTbl.dateCreated 
FROM         AccntTbl 
LEFT OUTER JOIN
     (SELECT idAccnt, 
             SUM(amount) AS Supply
     FROM      SuppyTbl AS SupplyTbl1
     WHERE      (MONTH(dateCreated) BETWEEN @from AND @to) AND
                (YEAR(dateCreated) = @year) AND fund = @fund
     GROUP BY     idAccnt) AS SupplyTbl     
WHERE  YEAR(AccntTbl.dateCreated) = @year AND 
            AccntTbl.office = @office AND
             AccntTbl.fund = @fund 
GROUP BY AccntTbl.Id,
          AccntTbl.accnt,
            AccntTbl.appro,
              Supply.Supply

and here to call from VB.NET

  conn.Open()
    Dim cmd As New SqlCommand("Test_SP", conn)
    With cmd
        .CommandType = CommandType.StoredProcedure
        .Parameters.Add("@from", SqlDbType.VarChar).Value = Int32.Parse(TextBox1.Text)
        .Parameters.Add("@to", SqlDbType.VarChar).Value = Int32.Parse(TextBox2.Text)
        .Parameters.Add("@year", SqlDbType.VarChar).Value = Int32.Parse(YearLbl.Text)
        .Parameters.Add("@office", SqlDbType.VarChar).Value = Dashboard.OfficeTxt.Text
        .Parameters.Add("@fund", SqlDbType.VarChar).Value = FundLbl.Text
    End With

How can I retrieve just some of its field in the TEST_SP like I just only want AccntTbl.Accnt and ISNULL(SupplyTbl.Supply, 0) as supply

Here's the example code that I already do

Dim cmd As New SqlCommand("Select AccntTbl.Accnt, ISNULL(SupplyTbl.Supply, 0) as supply from Test_SP", conn)

I know it's wrong and it says 'Could not find stored procedure'... lol, glad for any help :)

You can not. You get all the set or you change the procedure to return only the columns you want. — shadow, Jan 08 '16 at 11:11
@shadow is correct. Another option is pass an additional parameter to either include all or any of the columns you want... — Trevor, Jan 08 '16 at 11:15
you could use a function instead of a procedure and then only select the columns you want, additionally you allready check for isnull within the procedure so you do not need to check again — Markus, Jan 08 '16 at 11:19
This [SO post](http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure) has a few options in the second and third answers using temp tables, which you may want to explore.. — vmachan, Jan 08 '16 at 11:20
so there is no way the limit the set ? rather than to change the procedure itself ? — yoyie yoyie, Jan 08 '16 at 12:28
If you don't expect a lot of data to return it's fine to call this procedure and use only the columns you want. But if a lot of data is expected to return then I would change it or -in case that I couldn't because is used from somewhere else-, I would create a lighter version of it with just the columns I want . — shadow, Jan 08 '16 at 12:38
Once you get the results into a dataset, .net lets you edit the datatables in the dataset. I'm sure you can drop the columns you don't want if you really need to. — Tab Alleman, Jan 08 '16 at 13:57

score 0 · Answer 1 · edited May 23 '17 at 11:59

One way is to use user defined table valued function:

CREATE FUNCTION [dbo].[Test_SP]
(       @from int,
        @to int,
        @year int,
        @office varchar(MAX),
        @fund varchar(MAX)
)
RETURNS TABLE
AS
RETURN (
  SELECT AccntTbl.Id,
         AccntTbl.accnt,
         ISNULL(SupplyTbl.Supply, 0) AS Supply,
         AccntTbl.office,
         AccntTbl.exp,                 
         AccntTbl.dateCreated 
  FROM AccntTbl 
  LEFT JOIN
     (SELECT idAccnt, 
             SUM(amount) AS Supply
     FROM SuppyTbl AS SupplyTbl1
     WHERE MONTH(dateCreated) BETWEEN @from AND @to 
       AND YEAR(dateCreated) = @year 
       AND fund = @fund
     GROUP BY idAccnt) AS SupplyTbl     
  WHERE YEAR(AccntTbl.dateCreated) = @year 
    AND AccntTbl.office = @office 
    AND AccntTbl.fund = @fund 
  GROUP BY AccntTbl.Id,
           AccntTbl.accnt,
           AccntTbl.appro,
           Supply.Supply
);

Query:

SELECT Accnt, Supply
FROM [dbo].[Test_SP](1, 12, 2015, 'Baltimore', 'aaa');

Note that conditions YEAR(dateCreated) = @year and MONTH(dateCreated) are nonSARGable so if there is index on dateCreated column query optimizer will skip it.

How to do this in stored procedure?

1 Answers1