how to assign dynamic sql query to a cursor

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

SQL Server Development (2000)

how to assign dynamic sql query to a cursor

Author

Topic

soadfan
Starting Member

4 Posts

Posted - 2004-12-10 : 17:50:15

hi, i have the following dynamic query in my stored proc:

set @queryFront =
'SELECT
blah, blah'
set @queryMID =
'FROM blah blah
set @queryTail =
'where blah blah'

exec (@queryFront+@queryMID+@queryTail)

but after i execute it i need to put the results in a cursor.

i tried doing this:

DECLARE myCursor CURSOR local for
exec (@queryFront+@queryMID+@queryTail)

but that doesnt work. how do i assign dynamic sql to a cursor?

thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-10 : 17:52:52

Wow! Dynamic SQL with cursors. Dynamic SQL is bad enough, but include cursors in it, well out goes performance not to mention security.

And you don't be able to do this with cursors unless the entire command is inside the exec as you'll lose scope with dynamic SQL.

Why not post some sample data, table layout, and the expected result set using the sample data so that we can help you write an efficient query.

Tara

mprolli
Starting Member

24 Posts

Posted - 2004-12-10 : 23:24:03

Hey, Tduggan.. First let me say "congradulations", secondly, I have a question about Dynamic SQL. Seeing, by your comment above, that you are pretty much against it, how would you pass a varchar variable into a statement without using Dynamic Sql? (please forgive me if this is a stupid question, I'm still new..)

Thanks,

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-11 : 10:04:24

quote:
Originally posted by mprolli

Hey, Tduggan.. First let me say "congradulations", secondly, I have a question about Dynamic SQL. Seeing, by your comment above, that you are pretty much against it, how would you pass a varchar variable into a statement without using Dynamic Sql? (please forgive me if this is a stupid question, I'm still new..)

Thanks,

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

By using a stored procedure with parameters. Can you give an example of what you are trying to do?

- Jeff

mprolli
Starting Member

24 Posts

Posted - 2004-12-11 : 18:26:49

JSMITH, I don't understand your response. However, to elaborate on my previous post, what I'm asking is, if a asp page is passing you a TEXT value, how do you use it without using Dynamic SQL (see below)

Passed Variable @tablename = 'dbo.TABLENAME1'
passed variable2 @dbname = 'SQLSERVER1'

CREATE PROC TEST1
@tablename nvarchar(20),
@dbname nvarchar(20)
as

Select *
from @dbname + @tablename
order by ID

The above Syntax will not work.
it needs to be

ALTER PROC TEST1
@tablename nvarchar(20),
@dbname nvarchar(20)
AS

Declare @sql1 varchar(4000)

set @sql1 = 'Select * From '+@dbname+''+@tablename' order by ID'
--print (@sql1)
Exec (@sql1)

and please before the posts start flowing in, this is not what I'm trying to do, I am simply asking for an Alternative to Dynamic SQL, seeing as though it is a "Security Risk".

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-11 : 18:38:31

If you are doing that, why bother using a stored procedure at all? What you have described IS dynamic SQL, whether the page directly executes the SQL or a stored proc does. If your goal is to have your apps interface in that manner with your db, don't bother with stored procedures at all.

A stored procedure should never accept parameters that are database objects, or that are T-SQL commands -- it is supposed to serve as a LAYER between the physical Db and the applications. The App should not know or care how the DB is physically structured -- the SP serves as an interface. Doing what you have described completely defeats the purpose of even using SP's -- the app MUST KNOW the structure of the DB explicitly to make calls to the database, otherwise the dynamic SQL either passed in or generated will fall.

Your apps should be communicating through stored procedure NOT by passing in column names and table names and WHERE expressions, but rather by calling a stored procedures that are already hard-coded and optimized to accept parameters needed to return data the app needs.

- Jeff

mprolli
Starting Member

24 Posts

Posted - 2004-12-11 : 19:07:51

Ok, I guess you didn't read this part.....

quote:
Originally posted by mprolli

and please before the posts start flowing in, this is not what I'm trying to do, I am simply asking for an Alternative to Dynamic SQL, seeing as though it is a "Security Risk".

Reguarless of the situation, or which statement the parameter falls in, Sometimes Varchar or Nvarchar values ARE passed into queries. So the Question WAS, How would you handle a passed varchar/nvarchar value, if NOT in a Dynamic SQL statement.

I'm tellin ya, You bean-town boys KILL me, the red sox win the series and you guys think you run the world..(lol)

P.S. <----- Orginally From Boston. (and yes a RedSox nation memeber)

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-11 : 19:40:06

quote:

So the Question WAS, How would you handle a passed varchar/nvarchar value, if NOT in a Dynamic SQL statement.

Of course varchar's are passed to stored procedures. they're called parameters. You've already shown you know how to use them, so I don't understand what you are confused with, if Dynamic SQL isn't what you were looking for.

Did you not know you can do stuff like:


create procedure ReturnValue @SomeVarChar varchar(100), @SomeChar char(1)
as
if @SomeChar='A'
  select * from SomeTable where Value = @SomeVarChar
else
  select @SomeChar

without using dynamic SQL ? just refernce the parameters in your T-SQL statements.

what you asked, and the example you gave, strongly implied that your parameters aren't simply VarCHAR values you wish to use in T-SQL, but rather something with which to build dynamic SQL statements.

- Jeff

mprolli
Starting Member

24 Posts

Posted - 2004-12-11 : 19:51:09

I have written if statement like that in the past, however, my post was in response to Tduggans view of Dynamic SQL QUOTE: "..Dynamic SQL is bad enough"....., and I understand that they Can be if not written correctly, however, it sounded like a pretty broad statement, cataloging all Dynamic Statements, so I was wondering if she never uses Dynamic SQL, how she accounts for passed varchar/nvarchar varaibles. No biggie

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-12 : 22:39:02

quote:

Reguarless of the situation, or which statement the parameter falls in, Sometimes Varchar or Nvarchar values ARE passed into queries. So the Question WAS, How would you handle a passed varchar/nvarchar value, if NOT in a Dynamic SQL statement.

you pass them as parameters?

create proc mysp
@var1 varchar(10)
,@nvar1 nvarchar(10)
as....

what jeff have said, you should not expose your database schema, create sprocs and udfs to manipulate or select the data. what if the table/server you've passed were different from what you expected? the statement will still be executed coz it's a dsql.

unless you have a validation of some sort in your application or within the sproc. which is double work...

suggestion: create sprocs and udfs that will accept criteria like

create proc mysp
@var1 varchar(10)
,@nvar1 nvarchar(10)
as
select field1,field2,field3,field4 from myTable
where field1=@var1 and field2=@nvar1

--------------------
keeping it simple...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-13 : 12:29:38

mprolli,

Your example of dynamic SQL is not a realistic example. If you provided realistic examples, then we'd be able to show you the tricks in getting it to work non-dynamic SQL way. And Jeff pretty much covered anything that I'd have to say.

And I only use dynamic SQL for DBA type scripts not in application code. I so far have always had a workaround. That's not to say that there always will be though. But it should be avoided at all costs due to performance and security reasons. Just post your dynamic SQL questions here, and we'll be able to help you rewrite them. But please post real examples and not what you posted already.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-13 : 12:30:24

And one more thing, you should read up on sp_executesql. It's the alternative to EXEC(@SQL).

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources