Stored Procedure Throws Error.

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)

Stored Procedure Throws Error.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-14 : 09:57:43

Hi All,
I am working on creating a stored procedure. It looks like this:
ALTER PROCEDURE dbo.StoredProcedure1
(
@varAI int,
@varSI int
)
AS
SELECT @varAI * FROM Game
/* SET NOCOUNT ON */
RETURN
But it says that there is an error near FROM. Now if I take out the @varAI it works fine. Why are my variables causing problems? The variables are supposed to be passed in from an ASP.NET 2.0 application.
David.

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-14 : 09:59:54

look at the syntax for select in BOL.

what do you want to do??

do you mean:
select @varAI, *
from Game

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-14 : 10:00:51

What are you trying to do?
Do you want to pass column names and retrieve records?
Give more details

Madhivanan

Failing to plan is Planning to fail

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-14 : 10:05:21

spirit - your suggestion with the , worked. What I don't understand is why I have to have it in the SQL Stored Procedure statement and not in the ASP.NET 2.0 SQL statement.
madhivanan - looks like my problem is temporarily fixed, but I am sure I will have more of them. I was trying to pass variables from the ASP.NET 2.0 program (e.g varAI = number of Active Inside games the user selected) and then perform a sql statement based upon those variables.

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-14 : 10:22:17

so you want a where clause???

i simply don't understand what you want to do...

Go with the flow & have fun! Else fight the flow

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-14 : 11:18:22

Spirit,
Here is my new code, which works fine:
select * into #temppopularity from GamePopularity WHERE uID LIKE @varUserName
select @varAI, * FROM Game G LEFT OUTER JOIN LastPlayed LP On G.ID=LP.gID 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)
The one thing that doesn't work is I can't do a SELECT TOP @varAI, it gives me a syntax error. But if I do a SELECT TOP 10 it works fine. Any ideas on how to get that to work?
David.

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

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-14 : 11:23:55

Found the answer here: http://www.sqlteam.com/item.asp?ItemID=233

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

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-14 : 11:37:08

Well...I thought I had it, but it is still throwing an error even with doing what that article said. Here is my code:
@varcommand1 ='select TOP ' + @varAI +', * FROM Game G LEFT OUTER JOIN LastPlayed LP On G.ID=LP.gID 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)'

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-14 : 11:40:25

the syntax is:
'select top ' + @varAI + ' * from ....' -- no comma.

i'd recommend you do
set rowcount @varAI
select * from ....
set rowcount 0

it will yield same results without dynamic sql.

Go with the flow & have fun! Else fight the flow

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-14 : 11:47:25

I'd go with rowcount but I am using multiple SQL statements combined by UNION ALL and I don't think I could put rowcount in-between each one? e.g.
set rowcount @varAI
select * from ... UNION ALL
set rowcount 0
set rowcount @varSI
select * from ... UNION ALL
set rowcount 0
Took out comma, think something else must be throwing the error. Its telling me that the syntax is wrong near ')' and '@varcommand1'
I just put set before @varcommand1 and that fixed that but I am still getting a problem with the ')'.
David.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-15 : 01:00:54

You need to use Dynamic SQL
Try this


set @varcommand1 ='select TOP ' + @varAI +', * FROM Game G LEFT OUTER JOIN LastPlayed LP 
On G.ID=LP.gID 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)'
Exec(@varcommand1 )

Madhivanan

Failing to plan is Planning to fail

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-15 : 10:21:31

Thanks it works now.

- 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