[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL setting variables

Posted on 2011-09-21
14
Medium Priority
?
246 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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