Writing SQL with variables - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Writing SQL with variables

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-24 : 16:56:29

Hi,
Do I have to write a procedure/trigger for this or can I acheive it via SQL?
1. Declare 2 variables
2. Select name, id FROM sysobjects WHERE type in ('U', 'S')
3. Then store the value of name, id into variables @tablename, @tableid.
4. Then display the variables
5. Go

ex:
declare @tablename varchar(800),
@tableid integer

select @tablename = name, @tableid = id
FROM sysobjects WHERE type in ('U', 'S')

select @tablename as TableName, @tableid as TableId
go

Ofcourse this only gives me a one row (the last one) instead of all.
Q1. How do get all rows?

Q2: I am trying to learn from BOL but is there simpler website/book where I can learn to read and write procedures step by step?
Thanks,
Sarat.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-24 : 17:11:34

First, you can achieve everything via SQL if you can do it in a procedure or a trigger. Second, a variable can only hold one value at a time. This can easily be accomplished via a cursor, but that's not what you or anyone wants. I'm sure someone, but not me, can give you a solution for your example without a cursor.

Tara

Edited by - tduggan on 03/24/2003 17:12:35

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-24 : 17:19:23

Ya, I am able to write this with a cursor but I wanted to know and learn if there is another way to accomplish this.
sarat.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 17:39:10

Why not

select name as tablename, id as TableID
FROM sysobjects WHERE type in ('U', 'S')

?????

- Jeff

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-24 : 17:49:47

Hmm.. You are funny!!
Ya, I started from the same sql, I wanted to pass the values to variables as I wanted to use those variables in further sql in getting rows and index info!!!!
;-)
sarat.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 19:02:56

How would you like to store values from multiple rows in the same variable?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-25 : 14:39:56

Insert the data into a table datatype (SQL Server 2000)

bol:

table
A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

Syntax

Note Use DECLARE @local_variable to declare variables of type table.

table_type_definition ::=
TABLE ( { column_definition | table_constraint } [ ,...n ] )

column_definition ::=
column_name scalar_data_type
[ COLLATE collation_definition ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
[ ROWGUIDCOL ]
[ column_constraint ] [ ...n ]

column_constraint ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}

table_constraint ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
| CHECK ( search_condition )
}

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-25 : 15:18:59

Is there some reason why a temporary variable declared as a table would not suffice here?

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources