Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Variable in a select query

Posted on 2014-04-29
9
Medium Priority
?
224 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
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 1000 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 1000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

971 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