USING @BID VARIABLE FOR TWO VALUE TYPES

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

epicazoAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
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
 
epicazoAuthor Commented:
thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.