samedi 28 janvier 2017

why select statement based on parameter( IF @SearchType= 0 SELECT...) returns only message "Command(s) completed successfully." and no result

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