SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 variables in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

john55
Starting Member

3 Posts

Posted - 03/20/2014 :  11:43:06  Show Profile  Reply with Quote
Hi Folks,

Quite new to all of this.The question I have is simple,can I place variables in a sql statement.

For example a normal sql statement may go like this:

SELECT * FROM Atable WHERE FieldOne = something and FieldTwo = something.
Is it possible to replace the "somethings" in the above line with variables i.e. the contents of a ListBox for example.

Here is the pseudocode
variable SUN = ListBox1.Item
variable MOON = ListBox2.Item

Then the above code would now look like this.
SELECT * FROM ATable WHERE FieldOne = SUN AND FieldTwo = MOON.

Can anyone tell me if this is possible,as I say very new to all this and I might be barking up the wrong tree(Probably)

many thanks
John James

Robowski
Posting Yak Master

101 Posts

Posted - 03/20/2014 :  12:35:05  Show Profile  Reply with Quote
I'm reading this as you wanting to turn a variable into an Array of sorts? A variable only really holds a single value unless you declare one as a table

what you could do is put the date into a hash table and then us an in and subquery

ie

use tempdb

CREATE TABLE #HashTable(ColumnOne int)

INSERT #HashTable
VALUES (1);
INSERT #HashTable
VALUES (2);
INSERT #HashTable
VALUES (3);
INSERT #HashTable
VALUES (4);

SELECT *
FROM MainTable
WHERE FeldOne IN
(SELECT ColumnOne FROM #HashTable)
Go to Top of Page

john55
Starting Member

3 Posts

Posted - 03/20/2014 :  12:47:37  Show Profile  Reply with Quote
Hi Robowski,

many thanks for answering and I think i get your idea.You have lost me a bit with Hash Tables But it's not so much wanting to turn a variable into an array,rather place a variable into the sql string itself,I suppose that variable could have any source including an array.
Your bit of code is interesting
SELECT *
FROM MainTable
WHERE FeldOne IN
(SELECT ColumnOne FROM #HashTable)

Am I right in thinking here that you can insert the elements of the array into a sql statement and if so that partly answers my question.

Many thanks
Jim James
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/20/2014 :  12:47:49  Show Profile  Reply with Quote
Here is a link that might help:
http://technet.microsoft.com/en-us/library/aa258839(v=sql.80).aspx

It depends on what you want to do, but yes you can use variables in SQL Statements. I can't tell if you want to use variables or build a Dynamic SQL string. Here is a small sample that may help with the use of variables:
DECLARE @Foo VARCHAR(50) = 'Sun';
DECLARE @Bar VARCHAR(50) = 'Moon';

SELECT *
FROM TableName
WHERE ColumnOne = @Foo AND ColumnTwo = @Bar
Go to Top of Page

john55
Starting Member

3 Posts

Posted - 03/20/2014 :  16:23:15  Show Profile  Reply with Quote
Hi Lamprey,
Thankyou for your posting.I think what I am trying to do is get a USER defined query as opposed to a PREDEFINED(by me) query,(or a dynamic query).

I first create a series of List(or combo) boxes,in which I place a series of items in the List for the user to click on.These items correspond to the field in the Database

I then have this vision of a skeleton SQL statement hanging around waiting to be filled with the items clicked on in the List Boxes,which I thought I could put into variables and get them to rematerialise in the sql statement,thats why I was asking could I place variables into the sql code becasue it's the only way I could think of altering the sql code to correspond with the varying values that will be entered into the boxes...does any of this make sense and is it possible?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/20/2014 :  16:46:32  Show Profile  Reply with Quote
I'm not sure I understand. If you have a set number of variables you want to use. You might want to use a stored procedure and pass them to it (similar to my example). If there is a variable number, you still can use a stored procedure, but things start to get more complicated and performance tends to suffer. Enter Dynamic SQL. DSQL has lots of power. But. like all powerful things. it can blow up in your face real quick.

Here is a link about DSQL that covers a lot of bases:
http://www.sommarskog.se/dynamic_sql.html
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000