Solved

Variable in a select query

Posted on 2014-04-29
9
220 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 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