Solved

Variable in a select query

Posted on 2014-04-29
9
219 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Throw Error 7 35
Convert VBA UDF to SQl SERVER UDF 4 52
T-SQL: Stored Procedure Syntax 3 34
Checking for column changes SQL 2014 4 25
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

696 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