For some reason, I can't get the syntax right for this stored procedure. I have 2 variables. The issue is this - When we execute the stored procedure, the user may need to supply 1 integer for a parameter, multiple, or none. I want to allow all options to the user in this procedure, for both parameters. Below is what I have, at the moment. I keep trying different methods, and it seems like this should be simple to solve.
Right now, it doesn't seem to like the ELSE. I get an incorrect syntax near the word 'ELSE'. When I take those out, I get result windows for all 4 queries, even though only 1 of those IFs can possibly be true.
If anyone can help, I would be most appreciative!
ALTER PROCEDURE sp_ProjectDocs_AuditDB_ByLB
@BID VARCHAR = NULL, @LID VARCHAR = NULL
AS
BEGIN
SET NOCOUNT ON
--DECLARE @BID VARCHAR
--, @LID VARCHAR
--SET @BID = '301,316,373,322,331'
----'301,316,373,322,331'
--SET @LID = '1'
----'1,2,3,4'
IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') = ''
PRINT 'BOTH NULL'
BEGIN
SELECT VWPD.*
FROM vw_ProjectDocs_AuditDB VWPD
END
ELSE
IF ISNULL(@BID,'') = '' AND ISNULL(@LID,'') <> ''
PRINT 'LID NULL'
BEGIN
SELECT VWPD.*
FROM vw_ProjectDocs_AuditDB VWPD
WHERE VWPD.LID IN (@LID)
END
ELSE
IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') = ''
PRINT 'BID NULL'
BEGIN
SELECT VWPD.*
FROM vw_ProjectDocs_AuditDB VWPD
WHERE VWPD.BID IN (@BID)
END
ELSE
--IF ISNULL(@BID,'') <> '' AND ISNULL(@LID,'') <> ''
PRINT 'NEITHER NULL'
BEGIN
SELECT VWPD.*
FROM vw_ProjectDocs_AuditDB VWPD
WHERE VWPD.LID IN (@LID) AND VWPD.BID IN (@BID)
END
END
GO
--EXEC sp_ProjectDocs_AuditDB_ByLB '301','1'
GO
Thank you!
Aucun commentaire:
Enregistrer un commentaire