Created
July 12, 2017 19:05
-
-
Save carlosdlf/d0aa65e911dc8923e36ae9639ae8dbd4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ================================================ | |
-- Template generated from Template Explorer using: | |
-- Create Procedure (New Menu).SQL | |
-- | |
-- Use the Specify Values for Template Parameters | |
-- command (Ctrl-Shift-M) to fill in the parameter | |
-- values below. | |
-- | |
-- This block of comments will not be included in | |
-- the definition of the procedure. | |
-- ================================================ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
-- ============================================= | |
-- Author: <Disc0rd,,Carlos Daniel Larico Flores> | |
-- Create date: 27/06/2017 | |
-- Description: Retorna la lista de menu por usuario y RUC | |
-- ============================================= | |
IF EXISTS ( SELECT * FROM sysobjects | |
WHERE id = object_id(N'sp_menu_by_user') | |
AND type IN ( N'P', N'PC' ) ) | |
BEGIN | |
DROP PROCEDURE [dbo].[sp_menu_by_user] | |
END | |
GO | |
CREATE PROCEDURE sp_menu_by_user | |
@user nvarchar(20), | |
@ruc nvarchar(11) | |
AS | |
BEGIN | |
WITH menuTree (idmenu, cod_menu, des_menu, cod_menupadre, des_objecto) | |
AS | |
( | |
SELECT idmenu, cod_menu, des_menu, cod_menupadre, DES_OBJETO | |
FROM SEGU_TAB_MENU | |
WHERE COD_MENU IN (SELECT COD_MENU | |
FROM SEGU_TAB_ACCIONUSUARIO | |
WHERE COD_USUARIO = @user and COD_RUC = @ruc) | |
UNION ALL | |
--RECURSIVIDAD | |
SELECT e.idmenu, e.cod_menu, e.des_menu, e.cod_menupadre, e.DES_OBJETO | |
FROM SEGU_TAB_MENU AS e JOIN menuTree AS m | |
ON m.cod_menupadre = e.COD_MENU | |
) | |
-- Desplegar ResultSet | |
SELECT distinct * FROM menuTree; | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment