Solved

USING @BID VARIABLE FOR TWO VALUE TYPES

Posted on 2014-04-15
2
182 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now