I need to display certain number of column depending on what parameter end user is going to use. First I am declaring a table with SearchTypes based on Parameter then using that value to display necessary columns.
I am using statement
` IF @SearchType = 0 BEGIN
SELECT...
FROM
RETURN
END
IF @SearchType = 1 BEGIN
SELECT...
FROM
RETURN
END
`
But for some reason when I execute the statement I only see
Command(s) completed successfully.
without any result.
Why cant I simply display select
statement based on IF
condition without inserting it into #TempTable
?
DECLARE
@UserGUID UNIQUEIDENTIFIER = '1E82F47B-4C8F-4185-82D2-ED2EB59796D6',
@LineGUID UNIQUEIDENTIFIER = 'CF144437-F128-4B77-AC19-877247347D02'--'1CB72920-B3FC-4822-8030-37B50A2810EB'--, --WC
--------------------------------------------------------------------------------
--Populating @lineTable with GUIDs
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @LineTable Table(GUID uniqueidentifier)
IF @LineGuid IS NULL
BEGIN
INSERT INTO @LineTable
SELECT LineGuid From lstLines
END
ELSE
BEGIN
INSERT INTO @LineTable
SELECT * FROM dbo.StringOfGuidsToTable(@LineGuid,',')
END
--------------------------------------------------------------------------------
--creating @SearchType based on param @LineGUID
--in this example @SearchType gives 0
declare @SearchType int
set @SearchType= (select top 1
CASE WHEN lstLines.LineGUID IN (
'D657FDF6-19E1-425B-8598-295083424EFF', --eq access
'D4983D4A-1D12-461D-8837-6092DC74325B' --eq
) then 1
WHEN lstLines.LineGUID IN ('F58F7128-18A6-4A3D-AE36-0F30236B4050',--flood
'E05E7F4A-07C4-4981-BD13-2461D4EE4BF3') then 2--terrorism
WHEN lstLines.LineGUID = 'CF144437-F128-4B77-AC19-877247347D02' then 3 --wind
ELSE 0 end as SearchType
from tblQuotes
inner join lstLines ON tblQuotes.LineGUID = lstLines.LineGUID
where lstLines.Inactive = 0
and lstLines.LineGUID = @LineGUID)
--------------------------------------------------------------------------------
IF @SearchType = 0
BEGIN
SELECT DISTINCT
TOP 100 PERCENT
Column1,
Column2,
Column3
--other columns
FROM
dbo.tblNoteEntities (nolock)
--some other JOINs here
WHERE tq.LineGuid IN (SELECT * FROM @LineTable)
AND
(@UserGUID IS NULL OR tblNoteRecipients.UserGUID = @UserGUID)
ORDER BY Column1
RETURN
END
--------------------------------------------------------------------------------
if @SearchType = 1 --eq and eq access
BEGIN
SELECT DISTINCT
TOP 100 PERCENT
Column4,
Column5,
Column6
FROM
dbo.tblNoteEntities (nolock)
--some other JOINs here
WHERE tq.LineGuid IN (SELECT * FROM @LineTable)
AND
(@UserGUID IS NULL OR tblNoteRecipients.UserGUID = @UserGUID)
ORDER BY Column1
RETURN
END
--------------------------------------------------------------------------------
if @SearchType = 3 --And so on
Aucun commentaire:
Enregistrer un commentaire