?
Solved

USING @BID VARIABLE FOR TWO VALUE TYPES

Posted on 2014-04-15
2
Medium Priority
?
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 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