locked
Create a stored procedure which returns data RRS feed

  • Question

  • User-1506965535 posted

    I want to create a Stored procedure which will check 5 things from a table and they are as follows:-

    1. Admin Flag

    2. Warehouse_id

    3. User_id

    4. Delete_flag

    5.End_date

    After checking this it will return the exact warehouse_id from the table. 

    The table name is WMS_User_Rights in which Mkey is unique. Please suggest what to do

    Below is my Script for WMS_User_Rights table

    CREATE TABLE [dbo].[WMS_User_Rights](
    	[Mkey] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    	[Warehouse_Id] [numeric](10, 0) NOT NULL,
    	[Admin] [char](1) NOT NULL,
    	[User_Id] [numeric](10, 0) NOT NULL,
    	[Start_date] [datetime] NULL,
    	[End_date] [datetime] NULL,
    	[Tran_type] [varchar](10) NULL,
    	[Created_By] [numeric](10, 0) NULL,
    	[Created_date] [datetime] NULL,
    	[Last_Updated_By] [numeric](10, 0) NULL,
    	[Last_Updated_date] [datetime] NULL,
    	[Delete_Flag] [char](1) NOT NULL,
    	[Attribute1] [varchar](250) NULL,
    	[Attribute2] [varchar](250) NULL,
    	[Attribute3] [varchar](250) NULL,
    	[Attribute4] [varchar](250) NULL,
    	[Attribute5] [varchar](250) NULL,
    	[Attribute6] [varchar](250) NULL,
    	[Attribute7] [varchar](250) NULL,
    	[Attribute8] [varchar](250) NULL,
    	[Attribute9] [varchar](250) NULL,
    	[Attribute10] [varchar](250) NULL,
    	[Attribute11] [varchar](250) NULL,
    	[Attribute12] [varchar](250) NULL,
    	[Attribute13] [varchar](250) NULL,
    	[Attribute14] [varchar](250) NULL,
    	[Attribute15] [varchar](250) NULL
    ) ON [PRIMARY]
    
    GO

    Wednesday, November 25, 2015 7:28 AM

Answers

  • User1644755831 posted

    Hello Nadeem157,

    Please try this.

    CREATE PROCEDURE [dbo].[Getwarehouseid]
    @AdminFlag  char(1),
    @User_id numeric(10,0),
    @Delete_flag  char(1),
    @End_date datetime
    AS
    BEGIN
    	SET NOCOUNT ON;
      DECLARE @Warehouse_id INT
      SELECT 
        @Warehouse_id = Warehouse_id
      FROM dbo.WMS_User_Rights 
      WHERE 
      AdminFlag = @AdminFlag
      AND [User_id] = @User_id
      AND Delete_flag = @Delete_flag
      AND End_date = @End_date
    
    	SELECT @Warehouse_id
    END
    GO
    

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 26, 2015 3:30 AM
  • User269881539 posted

    Why put the result into a variable, why not just return directly?

    CREATE PROCEDURE [dbo].[Getwarehouseid]
    @AdminFlag  char(1),
    @User_id numeric(10,0),
    @Delete_flag  char(1),
    @End_date datetime
    AS
    BEGIN
    
      SET NOCOUNT ON;
    
      SELECT TOP 1 Warehouse_id
      FROM dbo.WMS_User_Rights 
      WHERE 
      AdminFlag = @AdminFlag
      AND [User_id] = @User_id
      AND Delete_flag = @Delete_flag
      AND End_date = @End_date
    
    END
    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 26, 2015 11:06 AM

All replies

  • User1644755831 posted

    Hello Nadeem157,

    Please try this.

    CREATE PROCEDURE [dbo].[Getwarehouseid]
    @AdminFlag  char(1),
    @User_id numeric(10,0),
    @Delete_flag  char(1),
    @End_date datetime
    AS
    BEGIN
    	SET NOCOUNT ON;
      DECLARE @Warehouse_id INT
      SELECT 
        @Warehouse_id = Warehouse_id
      FROM dbo.WMS_User_Rights 
      WHERE 
      AdminFlag = @AdminFlag
      AND [User_id] = @User_id
      AND Delete_flag = @Delete_flag
      AND End_date = @End_date
    
    	SELECT @Warehouse_id
    END
    GO
    

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 26, 2015 3:30 AM
  • User269881539 posted

    Why put the result into a variable, why not just return directly?

    CREATE PROCEDURE [dbo].[Getwarehouseid]
    @AdminFlag  char(1),
    @User_id numeric(10,0),
    @Delete_flag  char(1),
    @End_date datetime
    AS
    BEGIN
    
      SET NOCOUNT ON;
    
      SELECT TOP 1 Warehouse_id
      FROM dbo.WMS_User_Rights 
      WHERE 
      AdminFlag = @AdminFlag
      AND [User_id] = @User_id
      AND Delete_flag = @Delete_flag
      AND End_date = @End_date
    
    END
    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 26, 2015 11:06 AM