variables in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

variables in sql

Author

Topic

john55
Starting Member

3 Posts

Posted - 2014-03-20 : 11:43:06

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 - 2014-03-20 : 12:35:05

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)

john55
Starting Member

3 Posts

Posted - 2014-03-20 : 12:47:37

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-20 : 12:47:49

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

john55
Starting Member

3 Posts

Posted - 2014-03-20 : 16:23:15

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-20 : 16:46:32

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources