CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

How can I get my TSQL Stored Procedure to return data?

I am starting out with "baby steps" with a Stored Procedure which will end up being somewhat complex. All I want to do at first is to create a temp table, populate a few of the fields with some bogus values, and then have the table be returned from the SP when it is called. Here it is:

CREATE Procedure [dbo].[priceVarianceTest]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime 
AS 

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);

create table #temp1
(
    Unit varchar(50),
    MemberNo varchar(42),
    MemberItemCode varchar(42),
    ShortName varchar(25),
    ItemCode varchar(50),
    WVItem varchar(25),
    WVItemCode varchar(25),
    WVDescription varchar(250),
    Week1Usage varchar(25),
    Week2Usage varchar(25),
    UsageVariance varchar(25),
    Week1Price varchar(25),
    Week2Price varchar(25),
    PriceVariance varchar(25),
    PercentageOfPriceVariance varchar(42)
)

-- now try to add some data and return it:
Update #temp1 set
Unit = 'LOS CARNEROS',
MemberNo = '1289',
MemberItemCode = '76Trombones';

select * from #temp1;

And here is what it is rendered as when I execute it from Server Explorer (after I enter 'bla' and a couple of dates when prompted for the parameter vals):

USE [CPSData]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[priceVarianceTest]
        @Unit = N'bla',
        @BegDate = N'2015-01-01',
        @EndDate = N'2016-01-01'

SELECT  @return_value as 'Return Value'

GO

I was hoping the "select *" would return some data, but all I get in the Results pane is:

    Return Value
----------------
1   0

Why does it not return something more like:

Unit        MemberNo    MemberItemCode  ShortName   . . .
----        --------    --------------  ---------   -----
LOS CARNEROS    1289        76Trombones Null        . . .

?

What need I do to get meaningful (albeit admittedly contrived) data back from the SP?

Upvotes: 0

Views: 34

Answers (1)

Fuzzy
Fuzzy

Reputation: 3810

Update #temp1 set
Unit = 'LOS CARNEROS',
MemberNo = '1289',
MemberItemCode = '76Trombones';

Will not work if there are no rows present.

You need to use INSERT rather than UPDATE:

INSERT INTO #temp1 (Unit,MemberNo,MemberItemCode)
VALUES('LOS CARNEROS','1289','76Trombones')

A MERGE might be what you looking for if you want to be able to do both. But since you are recreating the table within your stored proc then I dont see you using update any time.

Upvotes: 2

Related Questions