Selecting Rows Based Upon a Variable.

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

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting Rows Based Upon a Variable.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-27 : 15:39:13

I know that a SQL Select Statement should look like this:
Select * FROM tblname where colname='John'
But, what if I have a variable (varname) that contains this information. How do I do it then?
Select * FROM tblname where colname=varname doesn't work. But neither does Select * FROM tblname where colname='varname'. Any ideas?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-27 : 15:42:07

select * from tblname where colName = @varname

read through SQL Help (a.k.a Books OnLine or BOL), this is pretty basic.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-27 : 15:42:44

I tried that. It doesn't appear to returning anything. :-/ Could it be possible that if I don't have an explicit CREATE TABLE statement and just a SELECT * INTO #temptable statement that it doesn't actually create the table?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-27 : 15:48:10

are you setting the variable??

[suspicious question]
Is this variable a SQL variable, or something else like ASP, VB etc?
[/suspicious question]

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-27 : 15:49:13

Its a ASP.NET 2.0 variable, passed in as a parameter. The variable is declared. Here is my complete stored procedure:
ALTER PROCEDURE dbo.SelectWeeklyGames
(
@varUserName varchar(200),
@varCommand varchar(8000)
)
AS
select * into #temppopularity from GamePopularity WHERE uID LIKE @varUserName
select * into #templastplayed from LastPlayed WHERE uID LIKE @varUserName
Execute(@varCommand)
/* SET NOCOUNT ON */
RETURN

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-27 : 16:10:53

why like?? do you have wildcards
and what is varcommand?

#temppopularity and #templastplayed will not exist when you execute the varCommand. they are local to the current process and execute creates a new process to run from.

so its probably not your like @variable thats the problem, its the execute(@varCommand).

What are you trying to accomplish?

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 09:03:17

Hi,
What I have is this massive SQL statement that populates a GridView in ASP.NET 2.0. I have the @varCommand in an Execute statement because I was having great difficulty getting the SQL to work in the stored procedure, but it would work from outside, so I figured the easiest thing to do was just to pass it in. See I needed to do a SELECT TOP (num) * FROM table but the (num) was a variable which SQL Server doesn't like, so I had to do it in the ASP.NET script as a string so that by the time it go to the stored procedure (where the string is @varcommand) it would be an actual number and not a variable. So, that's why I need it. I guess I could create a global table but I'm going to have multiple people using the same stored procedure at the same time and I'd guess they'd overwrite each other? Is there any way to execute @varcommand without running a new procedure?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 09:15:09

no. the execute runs in its own 'area' always. You could add the temp table creation into the varCommand...

but... I would bet that you are making this more difficult than it has to be. just how big is this 'massive' SQL statement?? and why besides the Top n do you need dynamic SQL? Are you doing paging? How many records are you dealing with?

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 09:26:02

Here is my "massive" statement. :-p And probably relatively its not so big. I've only included the first two lines, but there are ten in total, but after seeing them you'll see they're mostly identical:
varCommand = "select TOP " & varAI & " * FROM Game G LEFT OUTER JOIN #templastplayed LP On [Last Played]=LP.LastPlayedDate LEFT JOIN #temppopularity GP on G.YourPopularity=GP.Popularity WHERE G.Type=1 AND (LP.gID IS NULL OR ((LP.LastPlayedDate < '" & varDate & "') AND (LP.uID LIKE '" & varUserName & "'))) AND ((GP.gID=G.ID AND GP.uID LIKE '" & varUserName & "') OR GP.gID IS NULL) " & _
" UNION ALL select TOP " & varSI & " * FROM Game G LEFT OUTER JOIN #templastplayed LP On [Last Played]=LP.LastPlayedDate LEFT JOIN #temppopularity GP on G.YourPopularity=GP.Popularity WHERE G.Type=2 AND (LP.gID IS NULL OR ((LP.LastPlayedDate < '" & varDate & "') AND (LP.uID LIKE '" & varUserName & "'))) AND ((GP.gID=G.ID AND GP.uID LIKE '" & varUserName & "') OR GP.gID IS NULL) " & _
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 10:10:48

I was thinking. What if I added the username to the name of the temporary table and then made it global rather than local? That way it would continue on even though it was executing two stored procedures and they wouldn't overwrite each other b/c the name would be something like #templastplayedjohndoe, and every user has a unique username?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 10:27:28

what about something like this:


Create Table #Results (blah blah...)

Set RowCount @varAI

Insert Into #Results
select * 
FROM Game G 
LEFT JOIN #templastplayed LP 
On [Last Played]=LP.LastPlayedDate 
LEFT JOIN #temppopularity GP 
on G.YourPopularity=GP.Popularity 
WHERE G.Type=1 
AND 
	(LP.gID IS NULL 
	OR 
		(LP.LastPlayedDate < @varDate
		AND LP.uID LIKE @varUserName)
	)
AND 
	(
	(GP.gID=G.ID AND GP.uID LIKE @varUserName) 
	OR GP.gID IS NULL
	) 


Set RowCount @varSI

Insert Into #Results
select * 
FROM Game G 
LEFT JOIN #templastplayed LP 
On [Last Played]=LP.LastPlayedDate 
LEFT JOIN #temppopularity GP 
on G.YourPopularity=GP.Popularity 
WHERE G.Type=2 
AND 
	(LP.gID IS NULL 
	OR 
		(LP.LastPlayedDate < @varDate 
		AND LP.uID LIKE @varUserName)
	) 
AND 
	(
	(GP.gID=G.ID AND GP.uID LIKE @varUserName) 
	OR GP.gID IS NULL
	)

--...etc

Set RowCount 0

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 10:38:39

That would probably work. Thanks. I'll see what I come up with. :-)
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 11:28:01

Okay, I've started that...But I'm running into a problem. Here is my stored procedure:
ALTER PROCEDURE dbo.SelectWeeklyGames
(
@varUserName varchar(200),
@varAI int
)
AS
select * into #temppopularity from GamePopularity WHERE uID LIKE @varUserName
select * into #templastplayed from LastPlayed WHERE uID LIKE @varUserName
set rowcount @varAI
drop table #results
select * into #results
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On [Last Played]=LP.LastPlayedDate
LEFT JOIN #temppopularity GP
On G.YourPopularity=GP.Popularity
WHERE G.Type=1 AND (LP.gID IS NULL OR ((LP.LastPlayedDate < getDate())
AND (LP.uID LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
select * From #results
RETURN
But I am getting the error message (from my ASP.NET 2.0 page): Column names in each table must be unique. Column name 'ID' in table '#results' is specified more than once.
Thanks.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 11:37:12

don't use '*' here:
select * into #results FROM Game G

You need to specify the columns you want to return. Basically, ID is in that list more than once (from different tables)....

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 11:54:28

Great, thanks. That basically fixed it. I renamed some columns using the as feature, since I couldn't just take them out. I do have one more problem though. I have three tables I'm joining. But let's pretend there is just two for now, it'll make it easier and I can just extend the logic to the third if I can get it working with two. The Game table has all this information about games and contains a blank field entitled "Last Played". The idea of the program is to pull from the LastPlayed table a subset (#templastplayed) that contains only the records of the current user. Then to join those records in such a way as that the game that has a matching id in game with the date in #templastplayed are joined. However, for whatever reason I am not getting any results, they are all returning null. I have an idea why this might be. I think it might be because I am joining on the LastPlayed field in the tables, and they never match according to the database, b/c the game table is empty. But they weren't ever supposed to match. It was supposed to match the ID of the Game table with the gID of the #templastplayed table and then insert the #templastplayed.LastPlayedDate field into the Game.LastPlayed field. Any ideas on this? Here is my new updated code:
ALTER PROCEDURE dbo.SelectWeeklyGames
(
@varUserName varchar(200),
/*@varCommand varchar(8000)*/
@varAI int
)
AS
select uID, gID, Popularity as Popularity2 into #temppopularity from GamePopularity WHERE uID LIKE @varUserName
select uID as uID2, gID as gID2, LastPlayedDate into #templastplayed from LastPlayed WHERE uID LIKE @varUserName
set rowcount @varAI
select * into #results
FROM Game G
LEFT OUTER JOIN #templastplayed LP
On [Last Played]=LP.LastPlayedDate
LEFT JOIN #temppopularity GP
On G.YourPopularity=GP.Popularity2
WHERE G.Type=1 AND (LP.gID2 IS NULL OR ((LP.LastPlayedDate < getDate())
AND (LP.uID2 LIKE @varUserName)))
AND ((GP.gID=G.ID AND GP.uID LIKE @varUserName) OR GP.gID IS NULL)
SET ROWCOUNT 0
select * From #results
/* SET NOCOUNT ON */
RETURN
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-28 : 12:36:54

Never mind. I figured it. It was an ASP.NET issue rather than an SQL issue. Thanks for all your help.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources