Solved

Variable in a select query

Posted on 2014-04-29
9
221 Views
Last Modified: 2014-04-29
Hello I have the below:

DECLARE @RXBATCH1 VARCHAR(10),@RXBATCH2 VARCHAR(10)
SET @RXBATCH1 =(STR( DAY( GETDATE() ) )) + 'ADMT'
SET @RXBATCH2 = (STR( DAY( GETDATE()-1 ) ))+ 'ADMT'

Open in new window


how to write my select query such that

select * from rxs where rxbatch in(@rxbatch1)

Open in new window

0
Comment
Question by:Star79
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

Expert Comment

by:xav056
ID: 40030585
select * from rxs where rxbatch in (SELECT @rxbatch1)
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40030627
SELECT * FROM rxs WHERE rxbatch = @rxbatch1
0
 

Author Comment

by:Star79
ID: 40030640
Both the above queries did not work. It did not return the data
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40030649
Then you need to explain your process in more detail.  Based on the information you provided they should work.  Provide some sample data if possible.
0
 
LVL 9

Expert Comment

by:xav056
ID: 40030720
If you aren't getting any results maybe your variable rxbatch1 is not what you think it is
after seting the variable
SET @RXBATCH1 =(STR( DAY( GETDATE() ) )) + 'ADMT'

do
SELECT @RXBATCH1
to see the value
0
 

Author Comment

by:Star79
ID: 40030732
Xav, very true the @rxbatch1 = 29 and not '29admt'

so the code
DECLARE @RXBATCH1 VARCHAR(10),@RXBATCH2 VARCHAR(10)
SET @RXBATCH1 =(STR( DAY( GETDATE() ) )) + 'ADMT'

Open in new window

is not concatenating 'ADMT' TO THE DAY
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 250 total points
ID: 40030745
DECLARE @RXBATCH1 VARCHAR(10),@RXBATCH2 VARCHAR(10)
SELECT @RXBATCH1 = CAST(DAY(GETDATE()) AS VARCHAR) + 'ADMT',
      @RXBATCH2 = CAST(DAY(GETDATE() - 1) AS VARCHAR) + 'ADMT'
0
 
LVL 9

Assisted Solution

by:xav056
xav056 earned 250 total points
ID: 40030749
SET @RXBATCH1 =CAST(DAY( GETDATE() ) AS varchar)  + 'ADMT'
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40030771
After a little research I found that STR returns a string of length 10 with the value right-justified so when you add characters "ADMT" and then convert it to a VARCHAR(10) by assigning it to a variable of that type and length you are stripping the "ADMT" off.

There are numerous ways around this (CAST, LTRIM, increase variable size, etc.) but that is your root problem.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

632 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