CODEWITHSUNDEEP
sql-serverstored-procedures
chetan kambli
chetan kambli

Reputation: 814

How to display the result of stored procedure in the temporary table?

I want to display the result of a stored procedure in a temporary table.

I have the following code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[insertpropertylisting]
    @propertyname VARCHAR(150),
    @propertyaddress VARCHAR(250),
    @propertyprice MONEY,
    @availableunits VARCHAR(100),
    @propertyid INT OUTPUT
AS
BEGIN
    INSERT INTO propertylisting (propertyname, propertyaddress, propertyprice, availableunits)
    VALUES (@propertyname, @propertyaddress, @propertyprice, @availableunits)

    PRINT @propertyname

    SELECT @propertyaddress AS 'address'
    SET @propertyid = SCOPE_IDENTITY()

    SELECT 
        @propertyname = propertyname,
        @propertyaddress = propertyaddress,
        @propertyprice = propertyprice,
        @availableunits = availableunits 
    FROM 
        propertylisting 
    WHERE 
        property_id = @propertyid

    RETURN @propertyid
END

CREATE TABLE #propertylisting
(
    propertyname VARCHAR(150),
    propertyaddress VARCHAR(250),
    propertyprice MONEY,
    availableunits VARCHAR(100),
    propertyid INT 
)

INSERT INTO #propertylisting
    EXEC [dbo].[insertpropertylisting] 

SELECT
    propertyname, propertyaddress, propertyprice, availableunits 
FROM
    #propertylisting

DROP TABLE #propertylisting

I am not getting the output as table.

Upvotes: 1

Views: 2032

Answers (2)

EzLo
EzLo

Reputation: 14209

A few problems that I can see:

  • Not supplying parameters to the SP.
  • Stand-alone SELECT in the middle of SP that you don't want (also a PRINT).
  • The lack of a batch separator when issuing the ALTER PROCEDURE command.
  • You are assigning the values of the inserted row back to the variables and not actually selecting them back to the caller.
  • It's recommended to always include the column list on INSERT statements, and specially when doing INSERT INTO EXEC.

First make some changes to the SP; remove unused statements and parameters (including the OUT property of the parameter since it seems you aren't using it). You can return inserted values with the OUTPUT clause of the INSERT statement, including computed and IDENTITY columns.

ALTER procedure [dbo].[insertpropertylisting]
    @propertyname varchar(150),
    @propertyaddress varchar(250),
    @propertyprice money,
    @availableunits varchar(100)
as 
BEGIN

    insert into propertylisting (
        propertyname,
        propertyaddress,
        propertyprice,
        availableunits)
    OUTPUT
        inserted.propertyname,
        inserted.propertyaddress,
        inserted.propertyprice,
        inserted.availableunits,
        inserted.property_id -- Can return IDENTITY and computed columns
    values(
        @propertyname,
        @propertyaddress,
        @propertyprice,
        @availableunits)

END

After you run this ALTER, you can bring inserted records with a INSERT INTO EXEC. Make sure to pass proper values to the SP.

Create table #propertylisting
(
    propertyname varchar(150),
    propertyaddress varchar(250),
    propertyprice money,
    availableunits varchar(100),
    propertyid int 
)

Insert into #propertylisting (
    propertyname,
    propertyaddress,
    propertyprice,
    availableunits,
    propertyid)
Exec [dbo].[insertpropertylisting] 
    @propertyname = 'Value',
    @propertyaddress = 'Value',
    @propertyprice = 999,
    @availableunits = 'Value'

select 
    propertyname,
    propertyaddress,
    propertyprice,
    availableunits,
    propertyid
from 
    #propertylisting

Drop table #propertylisting

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

Try to use like this first create temp table and then execute the sql procedure 

  USE [Joins]
GO
/****** Object:  StoredProcedure [dbo].[insertpropertylisting]    Script Date: 1/29/2019 3:55:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insertpropertylisting]
@propertyname varchar(150),
@propertyaddress varchar(250),
@propertyprice money,
@availableunits varchar(100),
@propertyid int OUTPUT
as 
BEGIN
       insert into propertylisting(propertyname,propertyaddress,propertyprice,availableunits)
       values(@propertyname,@propertyaddress,@propertyprice,@availableunits)
       print @propertyname
       select @propertyaddress as 'address'
       SET @propertyid=SCOPE_IDENTITY()

       SELECT @propertyname=propertyname,@propertyaddress=propertyaddress,@propertyprice=propertyprice,
        @availableunits=availableunits ,@propertyid FROM propertylisting WHERE property_id=@propertyid

    --   Return @propertyid u can write in selecting it self 




END

Create table #propertylisting
(
propertyname varchar(150),
propertyaddress varchar(250),
propertyprice money,
availableunits varchar(100),
propertyid int 
)
Insert into #propertylisting
Exec [dbo].[insertpropertylisting]

Upvotes: 1

Related Questions