Declaring internal Variables in a SP

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)

Declaring internal Variables in a SP

Author

Topic

Valentin-
Starting Member

7 Posts

Posted - 2003-03-25 : 16:31:27

Hi,
I am trying to declare some internal vars in a Stored Procedure to transport them to a
second Select-Statement. But I get error.


ALTER PROCEDURE results
	(
		@Round VARCHAR
		
	)
AS	
	DECLARE @LackaID INT
	DECLARE @RoundID INT
	DECLARE @CircuitID INT
	

	SELECT R.round_id, R.round_name, R.round_courtid, R.round_date, R.round_weather, U.player_id, U.player_name, U.player_userid,
	CI.circuit_name,CI.circuit_id, CI.circuit_number, CI.circuit_difficulty, CI.circuit_pair, CI.circuit_image, C.court_difficulty, 
	C.court_circuits,C.court_price, C.court_description, C.court_image
	 FROM 
	user_round R LEFT OUTER JOIN user_player U ON R.round_id = U.player_round LEFT OUTER JOIN user_court C ON C.court_id = R.round_courtid
	LEFT OUTER JOIN user_circuit CI ON CI.circuit_courtid = C.court_id  WHERE round_id = @Round
	ORDER BY U.player_name
		
	SELECT results_hits FROM user_results WHERE results_userid = @LackaID
	AND results_round = @RoundID AND results_circuitid = @CircuitID ORDER BY results_circuitid

I am trying to get values for @RoundID, @LackaID and @RoundID.
@RoundID should have the value of R.round_id
@LackaID should have the value of U.player_userid
@CircuitID should have the value of CI.Circuit_id

Could anyone help me or just give me some tipps how to solve it!
With pleasure

Valentin-

Bye!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 16:41:57

Well what is the error that you are getting?

At first glance, I would suspect that the problem is that you aren't setting the variables to anything. You have them in your WHERE clause but the variables haven't been assigned yet. If the values are going to come from the first select statement, then you need to add them to the SELECT statement, such as:

SELECT @RoundID = R.round_id, etc...

Tara

Edited by - tduggan on 03/25/2003 16:43:35

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-25 : 17:14:02

quote:

I am trying to get values for @RoundID, @LackaID and @RoundID.

Not sure what you are saying ... you are trying to STORE values INTO @RoundID, etc... ? or you are trying to filter the data using values that are in those variables?

Because your second select statement:

quote:

SELECT results_hits FROM user_results WHERE results_userid = @LackaID
AND results_round = @RoundID AND results_circuitid = @CircuitID ORDER BY results_circuitid

is not storing anything into those variables; it is filtering the SELECT statement using those the contents of those variables. As Tara points out, you are never storing data into ANY variables in this stored procedure -- they are all NULL.

- Jeff

Valentin-
Starting Member

7 Posts

Posted - 2003-03-27 : 12:42:08

Okay I see it is not possible to fill temporary variables with
SELECTs like my first select, just to use them in my second
select?
Do you have another idea how solve it? Using to stored procedures?
Or Something completly different?
What's about subselects?

Edited by - Valentin- on 03/27/2003 12:42:41

Edited by - Valentin- on 03/27/2003 12:44:14

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-27 : 13:33:49

What are you trying to do?

Did you see tara's post on how to assign variables with a SELECT statement?

There's also the SET command ... read books on-line for info on that one.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-27 : 13:42:40

quote:

Okay I see it is not possible to fill temporary variables with
SELECTs like my first select, just to use them in my second
select?
Do you have another idea how solve it? Using to stored procedures?
Or Something completly different?
What's about subselects?

What you are doing is completely possible. You just aren't doing it correctly. If you want a value to be put into a variable, you have to specify that in the code. Please see the bold print below. I do not claim that this will work, but this should at least give you an idea of what to do.

SELECT @RoundID = R.round_id, @CircuitID = CI.circuit_id
FROM
user_round R LEFT OUTER JOIN user_player U ON R.round_id = U.player_round LEFT OUTER JOIN user_court C ON C.court_id = R.round_courtid
LEFT OUTER JOIN user_circuit CI ON CI.circuit_courtid = C.court_id WHERE round_id = @Round
ORDER BY U.player_name

Also, you will have to get the values into the variables in one step. Then if you want a record set returned (which is all of the other columns that I omitted above), then you will have to do that in another step. You can not combine these.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources