1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| CREATE PROCEDURE [dbo].[research]
-- Add the parameters for the stored procedure here
@id int,
@p1 nvarchar (50),
@p2 nvarchar (50)
AS
BEGIN
declare @result nvarchar(50)
DECLARE @request nvarchar(MAX) = 'SELECT DISTINCT AC.name, AC.firstname FROM ACT AS AC, USER AS US, USER_L AS USL
WHERE AC.id = US.id'
DECLARE c1 CURSOR FOR
SELECT name FROM RESEARCH WHERE id = @id
OPEN c1
FETCH c1 INTO @result
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@result = 'exp')
SET @request = @request + ' AND USL.id IN(SELECT id FROM L WHERE name LIKE ( ''% '+@p1+' %'' ) )
OR AC.name LIKE (''% '+@p1+' %'')'
IF (@result = 'lan')
SET @request = @request + ' AND AC.name LIKE ''%'+@p2+'%'''
FETCH NEXT FROM c1 INTO @result
END
CLOSE c1
DEALLOCATE c1
EXECUTE (@request);
END |
Partager