Solved

SQL setting variables

Posted on 2011-09-21
14
240 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 250 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 250 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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