Solved

SQL setting variables

Posted on 2011-09-21
14
236 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
Comment Utility
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
Comment Utility
I will try that and see how it works.  Thanks.
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
Comment Utility
In SQL Server 2008, you can also combine them into one statement.

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

Greg

0
 

Author Comment

by:simplyfemales
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What does this do?

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

/*CMS ROI SECTION*/
SELECT @requestDate, @requestClient
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Expert Comment

by:dqmq
Comment Utility
And what environment are you running the tsql script from?
0
 

Author Comment

by:simplyfemales
Comment Utility
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
Comment Utility
You can't use IN like that.   [NABP #] IN(@requestStore) should be [NABP #] = @requestStore.

Greg

0
 

Author Comment

by:simplyfemales
Comment Utility
Jesters... changed to your suggestion, still the same result.

DECLARE        OK
0
 

Author Comment

by:simplyfemales
Comment Utility
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
Comment Utility
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
Comment Utility
Worked just fine inside Server Studio.  Not sure what the problem with Navicat is.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now