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

x
?
Solved

SQL setting variables

Posted on 2011-09-21
14
Medium Priority
?
245 Views
Last Modified: 2012-05-12
I have tried following instructions from MSDN and even from experts-exchange but for some reason I can't get variables to work properly.

I have the following sample query:


select A,B,C
from test
where date=[date needed]
and client=[client needed]
union all
select A,B,C
from test2
where date=[date needed]
and client=[client needed]

I would like to have a variable for the "date needed" and a variable for the "client needed"

The actual query is over 1000 lines long and I would rather program the variable into the query and then just let it be and if the variable ever needs to be changed, then I will have it accessible at the top of the query rather than doing a find and replace through 1000 lines of code.

I am under the impression I have to do a declare statement and also a SET statement.

Help please?
0
Comment
Question by:simplyfemales
  • 7
  • 4
  • 3
14 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 1000 total points
ID: 36574946
TSQL, right?

DECLARE @DATENEEDED datetime
DECLARE @CLIENTNEEDED int                 -- or varchar()
SET @DATENEEDED = '2011/09/20'
SET @CLIENTNEEDED = 5

select A,B,C
from test
where date=@DATENEEDED
and client=@CLIENTNEEDED
0
 

Author Comment

by:simplyfemales
ID: 36574997
I will try that and see how it works.  Thanks.
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 36575196
In SQL Server 2008, you can also combine them into one statement.

DECLARE
@DateNeeded DATETIME = '09/21/2011',
@ClientNeeded VARCHAR(50) = 'SomeClient'

Greg

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

 

Author Comment

by:simplyfemales
ID: 36575309
OK.  I have done the following

DECLARE

@requestDate DATE = '9/1/2011',
@requestClient varchar(50) = '0546070'

/*CMS ROI SECTION*/
SELECT ....................

When I execute, I get

DECLARE          OK

No results like before.  What am I missing?
0
 

Author Comment

by:simplyfemales
ID: 36575318
BTW the SELECT .............. is the beginning of the 1000 line query; I didnt' want to list the whole thing.  It functions just fine without the DECLARE stuff in front of it.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36575345
Did you substitute the variables, @requestDate and @requestClient, in all the places where it was hard coded in your script?

Greg

0
 
LVL 42

Expert Comment

by:dqmq
ID: 36575968
What does this do?

DECLARE
@requestDate DATE = '9/1/2011',
@requestClient varchar(50) = '0546070'

/*CMS ROI SECTION*/
SELECT @requestDate, @requestClient
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36575974
And what environment are you running the tsql script from?
0
 

Author Comment

by:simplyfemales
ID: 36576783
DECLARE
@beginDate date = '9/4/2011',
@endDate Date = '9/10/2011',
@requestStore varchar(7) = 0546070


/*CMS ROI SECTION*/
SELECT
      'Section',
      'Identity',
      COUNT(*)'A',
      SUM([Submitted Gross Amount Due])'B',
      0 'C',
      SUM([Submitted Gross Amount Due])'D',
      SUM([Submitted Gross Amount Due])'E',
      COUNT(*)'F',
      0 'G',
      COUNT(*)* 0.1 'H',
      0 'I',
      COUNT(*)* 0.1 'J'
FROM
      TableA
WHERE
      [NABP #] IN(@requestStore)
AND [Processing Date] BETWEEN @beginDate
AND @endDate

Still just getting DECLARE      OK

the interface program is Navicat and yes, TSQL
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36576796
You can't use IN like that.   [NABP #] IN(@requestStore) should be [NABP #] = @requestStore.

Greg

0
 

Author Comment

by:simplyfemales
ID: 36576878
Jesters... changed to your suggestion, still the same result.

DECLARE        OK
0
 

Author Comment

by:simplyfemales
ID: 36576888
If I don't have the variables Declared and just utilize the values in the proper locations of the WHERE clause, it runs just fine.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36578224
I suspect your issue is related to Navicat.  I"m not familiar with the product, but your symptoms are foreign to SQL Server:  I think they originate in Navicat.  In my experience, TSQL does NOT return "OK".  Ever.  That's coming from some software layered over SQL Server.  Can you even run a TSQL script from Navicat?

I do believe Navicat supports parameterized queries, but they are totally different from the way it is done in TSQL.  
0
 

Author Closing Comment

by:simplyfemales
ID: 36584359
Worked just fine inside Server Studio.  Not sure what the problem with Navicat is.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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