Create a stored procedure which returns data

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

Answer 1

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

Answer 2

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