Solved

SQL setting variables

Posted on 2011-09-21
14
241 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
[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
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

710 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