CODEWITHSUNDEEP
sql-serverstored-procedures
Jobin
Jobin

Reputation: 91

Stored procedure not accepting any value

I have written a stored procedure based on a set of process, I'm just passing a single parameter as input to the procedure but it seems it is not taking the value. But when I give input value instead of parameter in the procedure it is working.

There is no mistake in the flow of process, but seems something missing in the procedure syntax end.

below is the stored procedure I used.

ALTER PROCEDURE [TransferIn]
    @ponumber NVARCHAR = NULL
AS
BEGIN
    --step  1  Delete Temp Pur_ID 
    IF EXISTS (
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 'Pur_ID_IN')
        DROP TABLE Pur_ID_IN;

    -- =============================================
    --step 2 select PO Number  
    --IF @ponumber IS NOT NULL  
    SELECT 
        ponumber, id
    INTO Pur_ID_IN
    FROM purchaseorder
    WHERE potype IN (2, 4)
      AND status = 0
      AND ponumber = @ponumber;

    -- =============================================
    --step 3 
    --delete Temp. Tabel P_Test20_12_IN 
    IF EXISTS (
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 'P_Test20_12_IN')
        DROP TABLE P_Test20_12_IN;

    -- =============================================
    -- step 4 (Insert Data For Invoice To Temp Tabel After Group  )
    SELECT 
        ItemDescription, PurchaseOrderID,
        SUM(QuantityOrdered) AS QuantityOrdered,
        itemid, Price
    INTO P_Test20_12_IN
    FROM PurchaseOrderEntry
    WHERE PurchaseOrderID IN (SELECT id FROM Pur_ID_IN)
    GROUP BY 
        ItemDescription, StoreID, PurchaseOrderID,
        itemid, Price;
    --order by 3

    -- =============================================
    -- step 5 Delete Record From  PurchaseOrderEntry 
    DELETE PurchaseOrderEntry
    FROM PurchaseOrderEntry
    WHERE PurchaseOrderID IN (SELECT id FROM Pur_ID_IN);
    -- =============================================

    INSERT INTO [W07].[dbo].[PurchaseOrderEntry] ([ItemDescription], [LastUpdated], [PurchaseOrderID], [QuantityOrdered], [ItemID], [Price])
        SELECT 
            [ItemDescription],
            GETDATE() AS [LastUpdated],
            [PurchaseOrderID], [QuantityOrdered],
            [ItemID], [Price]
        FROM 
            P_Test20_12_IN;
END

Upvotes: 1

Views: 48

Answers (2)

Devart
Devart

Reputation: 122002

ALTER PROCEDURE [TransferIn]
(
    @ponumber NVARCHAR(100)
)
AS BEGIN

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
        DROP TABLE #temp

    CREATE TABLE #temp (id INT PRIMARY KEY)

    INSERT INTO #temp (id)
    SELECT /*DISTINCT*/ id
    FROM dbo.purchaseorder
    WHERE potype IN (2, 4)
        AND [status] = 0
        AND ponumber = @ponumber

    IF OBJECT_ID('tempdb.dbo.#temp2') IS NOT NULL
        DROP TABLE #temp2

    SELECT ItemDescription,
           PurchaseOrderID,
           SUM(QuantityOrdered) AS QuantityOrdered,
           itemid,
           Price
    INTO #temp2
    FROM PurchaseOrderEntry
    WHERE PurchaseOrderID IN (SELECT * FROM #temp)
    GROUP BY ItemDescription,
             StoreID, --?
             PurchaseOrderID,
             itemid,
             Price;

    DELETE PurchaseOrderEntry
    FROM PurchaseOrderEntry
    WHERE PurchaseOrderID IN (SELECT * FROM #temp)

    INSERT INTO [W07].[dbo].[PurchaseOrderEntry] ([ItemDescription], [LastUpdated], [PurchaseOrderID], [QuantityOrdered], [ItemID], [Price])
    SELECT [ItemDescription],
           GETDATE() AS [LastUpdated],
           [PurchaseOrderID],
           [QuantityOrdered],
           [ItemID],
           [Price]
    FROM #temp2

END

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

Problem is 

@ponumber nvarchar = null

change it to 

@ponumber nvarchar(max) = null

Note:If you do NOT specify the size(char, nchar, varchar, nvarchar), sql server will default to 1 character.

Upvotes: 2

Related Questions