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
 how to use variable in a cursor

Author  Topic 

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2006-12-11 : 23:11:22
if i have the cursor

cursor:

cursor for
select name
from sysobjects
where type='P' AND category='0'

and wants a variable before sysobjects which equals a table name, so itll be:

cursor for
select name
from @variable..sysobjects
where type='P' AND category='0'

how do i include a variable within a cursor statement

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2006-12-12 : 00:05:54
You can't do that unless you use Dynamic SQL.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-12 : 05:49:01
Can you explain what you are trying to do?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2006-12-13 : 17:54:50
nah just learning how to use a variable in a cursor if i even need it

trying to make the above work for practice
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2006-12-13 : 20:22:13
12 years ago, my SQL mentor told me that if I ever used a cursor in his presence, he would no longer be my mentor... sage and wise words.

--Jeff Moden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-14 : 05:40:37
quote:
Originally posted by Jeff Moden

12 years ago, my SQL mentor told me that if I ever used a cursor in his presence, he would no longer be my mentor... sage and wise words.

--Jeff Moden

1 Newbies are interested to make use of Cursor because it is one of asked questions in interview
2 Those who are using Front end with For and While loop, try to simulate the same row-by-row updates when they need to write queries
3 Those come from ORACLE prefer using Cursor as they dont know mostly things can be done in SET based approach

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-14 : 05:42:15
quote:
Originally posted by rnbguy

nah just learning how to use a variable in a cursor if i even need it

trying to make the above work for practice

SQL Server help file has informations

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2006-12-16 : 20:20:55
quote:
Originally posted by madhivanan
[br1 Newbies are interested to make use of Cursor because it is one of asked questions in interview
2 Those who are using Front end with For and While loop, try to simulate the same row-by-row updates when they need to write queries
3 Those come from ORACLE prefer using Cursor as they dont know mostly things can be done in SET based approach


Ya think?

1 Newbies should be taught quickly that cursors and most while loops simply mean a failure to find a set based solution. People who do interviews should love Newbies that have never used one. Newbies that inteview with companies that think cursors are good, should run like hell.

2 Those who are writing code for the front end are frequently "Java (or whatever) developers with some SQL experience". They shouldn't be allowed anywhere near the back end. My favorite "T" shirt has the following on it...

 SELECT *
   FROM Users
  WHERE Clue > 0
(0 Row(s) affected)


3 Isn't that the truth? And, they can't even help it... I'm sure you know this but you can't even write a set based trigger in Oracle... it forces you to do one row at a time. Still, we're teaching our Oracle group how to do things set based even in Oracle... they took it to heart and rewrote a batch job that used to take a whole weekend to run and got it down to an hour and a half. Cursors are bad, even in Oracle.

--Jeff Moden
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2006-12-17 : 19:02:49
okay smarty pants, how would u do what im trying to do using a set based approach, or not that i ask will this thread die..?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2006-12-17 : 19:27:03
You stated earlier that you're trying to make this work "for practice", then ask how to do what you're trying to do "set based". Without a concrete example it's useless to ask for our advice above and beyond what we've already given.

Which boils down to: set-based thinking is a good tool to have, while you can go an entire career never once using a cursor.
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2006-12-17 : 19:36:42
yeah i asked for practice but no1 helped but everyone insulted so i thought id make u guys happy and relate to ur approach...

all i need is to know what to do in a situation where I have a variable table name and want to perform a select statement on that table, now this table name would never be statically defined... so in case i use this in a stored procedure, it will accept a table name as a parramater then perform a query on that table.

Now that being done i want to also perform a query on that table names sysobjects...
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2006-12-17 : 20:30:52
This is not an insult, but what you're describing is an extremely poor practice. The previous comments are meant to discourage you from following it. It will cause you more trouble than it will help you. I would say it's not too bad for a one-off maintenance or administration type of activity (data cleansing, etc.) but as part of a normal application it's not worth it.

Here's a basic question: why ISN'T the table statically defined? Why would you NEED to access tables in a variable manner? If your database is designed correctly there will be no need for this kind of access. Same concept applies to accessing procedures and databases. You should have no need to query sysobjects just to make your application work. Your last reply still doesn't quite have the necessary detail of WHAT you're trying to accomplish (so far you've asked HOW to do it)

Even if you had a legitimate need to do this, using a stored procedure for it is counterproductive. A cursor won't help, and using dynamic SQL defeats the purpose of using a stored procedure.
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2006-12-17 : 21:25:00
okayyy hmmm let me try this differently:

I want to create 1 stored procedure that would perform a task, for now assume the simple task is to show me a list of all stored procedures in current database

now I don't want to have to create 1 stored procedure for each table only because the name of the table is statically defined in the stored procedure. I would like only 1 stored procedure which will accept a parramater (the name of the table), and that will be the table it works with.

does that make sense or not really?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2006-12-17 : 23:05:00
No. I assume you're trying to create a single stored procedure that can select from any table, is that correct?

If so, here's why passing table names is a bad practice:

1. Different tables have different column lists and will provide different results.
2. To make this work you have to select ALL columns even if you don't want or need all of them. Will also force you to return all rows from the table.
3. To make this work you have to grant SELECT permissions on all tables and columns, this is a security risk.
4. Since this requires dynamic SQL you are open to possible SQL injection attacks (more security risk)
5. This approach easily leads to tables and features constantly added and causing the database to grow unwieldly. i.e.: make it up as you go along vs. design
6. This approach treats tables as dumb data storage and isolated entities, and ignores relations between tables. If you're not linking tables together then don't use SQL Server.

As far as showing you all stored procedures in a database:

SELECT name FROM sysobjects WHERE type='P'

does the trick. You can also use the more standard:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'

If that's all you need then you don't need a cursor or dynamic SQL, and wrapping that in a stored procedure is OK but not really a time saver.
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2006-12-17 : 23:14:54
k ta
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-18 : 08:26:44
quote:
Originally posted by rnbguy

okayyy hmmm let me try this differently:

I want to create 1 stored procedure that would perform a task, for now assume the simple task is to show me a list of all stored procedures in current database

now I don't want to have to create 1 stored procedure for each table only because the name of the table is statically defined in the stored procedure. I would like only 1 stored procedure which will accept a parramater (the name of the table), and that will be the table it works with.

does that make sense or not really?


1 Did you create tables like Mytable0, Mytable1,... and pass that as parameter?
2 If not, read fully what Rob advised you
3 You should read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-18 : 08:35:07
quote:
Originally posted by Jeff Moden

quote:
Originally posted by madhivanan
[br1 Newbies are interested to make use of Cursor because it is one of asked questions in interview
2 Those who are using Front end with For and While loop, try to simulate the same row-by-row updates when they need to write queries
3 Those come from ORACLE prefer using Cursor as they dont know mostly things can be done in SET based approach


Ya think?

1 Newbies should be taught quickly that cursors and most while loops simply mean a failure to find a set based solution. People who do interviews should love Newbies that have never used one. Newbies that inteview with companies that think cursors are good, should run like hell.

2 Those who are writing code for the front end are frequently "Java (or whatever) developers with some SQL experience". They shouldn't be allowed anywhere near the back end. My favorite "T" shirt has the following on it...

 SELECT *
   FROM Users
  WHERE Clue > 0
(0 Row(s) affected)


3 Isn't that the truth? And, they can't even help it... I'm sure you know this but you can't even write a set based trigger in Oracle... it forces you to do one row at a time. Still, we're teaching our Oracle group how to do things set based even in Oracle... they took it to heart and rewrote a batch job that used to take a whole weekend to run and got it down to an hour and a half. Cursors are bad, even in Oracle.

--Jeff Moden

1 True.
2 Not neccessarily. In small companies, front end developers are also asked to write queries. I am basically a VB6 programmer and sometimes I also write queries
3 I have seen that all newbies always try to make use of Cursor

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site