Solved

USING @BID VARIABLE FOR TWO VALUE TYPES

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

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…

724 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