Solved

USING @BID VARIABLE FOR TWO VALUE TYPES

Posted on 2014-04-15
2
190 Views
Last Modified: 2014-04-15
Hello experts,

I am trying to create a query that I enter the @BID1 value once and it would pass it to 3 different queries.   One of them in which I use the LIKE function, but its taking too long.  I am not sure it is working.



use eiwdata 
go
[b]DECLARE @BID1 INT[/b]
SET @BID = '3195451'
SELECT * from (SELECT     TOP (1000) 
'INPUT' AS qTYPE, 
 inq_que_id AS qID, 
 CASE WHEN inq_status_id ='3' THEN 'READY' WHEN inq_status_id ='2' THEN 'READY' WHEN inq_status_id='1' THEN 'READY' ELSE 'UNK' END AS qSTATUS, 
 inq_batch_id AS bID, inq_batch_label AS bLabel, inq_total_docs AS bTotalDocs, 
                      inq_total_pages AS bTotalPgs, inq_create_user AS bCreateUsr, inq_create_datetime AS bCreateDt, inq_last_chg_user AS bLastUsr, inq_last_chg_datetime AS bLastDt, 
                      inq_message AS qMSG
FROM         dbo.EIWT_INPUT_QUEUES
union all
SELECT     TOP (100) PERCENT 'UPLOAD' AS qTYPE, upq_que_id AS qID, 
CASE WHEN upq_status_id ='0' THEN 'ERROR' WHEN upq_status_id='2' then 'READY' WHEN upq_status_id ='3' then 'IN' ELSE 'UNK' END AS qSTATUS, upq_batch_id AS bID, upq_batch_label AS bLabel, 
                      upq_total_docs AS bTotalDocs, upq_total_pages AS bTotalPgs, upq_create_user AS bCreateUsr, upq_create_datetime AS bCreateDt, upq_last_chg_user AS bLastUsr, 
                      upq_last_chg_datetime AS bLastDt, upq_message AS qMSG
FROM         dbo.EIWT_UPLOAD_QUEUE) a where a.bID=@BID1

-- Is there a way to get this VALUE from @BID1?
-- THIS PART, DOES'T SEEM TO WORK.  I AM NOT SURE IF THE "LIKE" SYNTAX IS WORKING
-- THE QUERY TAKES TOO LONG HERE
-- AUDIT RESULTS

USE audit
GO
DECLARE @BID2 nvarchar(250)
SET @BID2 = '3195451'
SELECT TOP 100 'AUDIT' AS qtype
      ,a.ENCOUNTER
      ,a.MRN 
      ,a.UserInstanceId 
      ,a.ACTION 
      ,b.ACTION_DESC 
      ,a.REMARK 
      ,a.OCCURRED 
      ,a.FACILITY 
  FROM [audit].[dbo].[Audit_Trail_2014] a
  inner join audit.dbo.action_table b on a.ACTION = b.ACTION 
  WHERE REMARK LIKE '%' + @BID2 + '%'

Open in new window

0
Comment
Question by:epicazo
2 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40001706
To GO is the batch terminator for SSMS. Thus your variable is only defined in the first batch, but not in the second. The simple solution is not to use GO. Especially as you're already using a two-point object notation, which identifies the databases.

 DECLARE @BID INT;
 SET @BID = '3195451';

 SELECT *
 FROM   ( SELECT TOP ( 1000 )
                    'INPUT' AS qTYPE ,
                    inq_que_id AS qID ,
                    CASE WHEN inq_status_id = '3' THEN 'READY'
                         WHEN inq_status_id = '2' THEN 'READY'
                         WHEN inq_status_id = '1' THEN 'READY'
                         ELSE 'UNK'
                    END AS qSTATUS ,
                    inq_batch_id AS bID ,
                    inq_batch_label AS bLabel ,
                    inq_total_docs AS bTotalDocs ,
                    inq_total_pages AS bTotalPgs ,
                    inq_create_user AS bCreateUsr ,
                    inq_create_datetime AS bCreateDt ,
                    inq_last_chg_user AS bLastUsr ,
                    inq_last_chg_datetime AS bLastDt ,
                    inq_message AS qMSG
          FROM      eiwdata.dbo.EIWT_INPUT_QUEUES
          UNION ALL
          SELECT TOP ( 100 ) PERCENT
                    'UPLOAD' AS qTYPE ,
                    upq_que_id AS qID ,
                    CASE WHEN upq_status_id = '0' THEN 'ERROR'
                         WHEN upq_status_id = '2' THEN 'READY'
                         WHEN upq_status_id = '3' THEN 'IN'
                         ELSE 'UNK'
                    END AS qSTATUS ,
                    upq_batch_id AS bID ,
                    upq_batch_label AS bLabel ,
                    upq_total_docs AS bTotalDocs ,
                    upq_total_pages AS bTotalPgs ,
                    upq_create_user AS bCreateUsr ,
                    upq_create_datetime AS bCreateDt ,
                    upq_last_chg_user AS bLastUsr ,
                    upq_last_chg_datetime AS bLastDt ,
                    upq_message AS qMSG
          FROM      eiwdata.dbo.EIWT_UPLOAD_QUEUE
        ) a
 WHERE  a.bID = @BID;

 SELECT TOP 100
        'AUDIT' AS qtype ,
        a.ENCOUNTER ,
        a.MRN ,
        a.UserInstanceId ,
        a.ACTION ,
        b.ACTION_DESC ,
        a.REMARK ,
        a.OCCURRED ,
        a.FACILITY
 FROM   audit.dbo.Audit_Trail_2014 a
        INNER JOIN audit.dbo.action_table b ON a.ACTION = b.ACTION
 WHERE  REMARK LIKE '%' + @BID + '%';

Open in new window

0
 

Author Closing Comment

by:epicazo
ID: 40001882
thanks!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question