Cursor on dynamic select statement

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)

Cursor on dynamic select statement

Author

Topic

DeDude
Starting Member

1 Post

Posted - 2003-04-09 : 06:21:19

I am trying to create a cursor inside a function that looks something like this :

CREATE FUNCTION FindBooks
(
@SearchFor nvarchar(50)
)

RETURNS @Books TABLE
(
Author nvarchar(50),
Title nvarchar(50)
)

AS
BEGIN

DECLARE @sql_statement nvarchar(1000),
@Author nvarchar(50),
@Title nvarchar(50)

SET @sql_statement = 'SELECT Author, Titles FROM Books '

IF @Author <> ''
SET @sql_statement = @sql_statement + 'WHERE Author LIKE ' + '%' +
@SearchFor + '%'

DECLARE my_cursor CURSOR FOR @sql_statement
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @Author, @Title
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @Books (Author, Title) VALUES (@Author, @Title)
FETCH NEXT FROM my_cursor INTO @Author, @Title
END
CLOSE my_cursor
DEALLOCATE my_cursor

RETURN

END

Problem is that sql can't seem to handle a dynamic sql statement for a cursor. The actual function uses 10 input variables which can be null or represent a numeric or a non-numeric value. Therefore I am in desparate need of a way to create dynamic sql statements. Anyone any idea?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-09 : 06:45:18

I think your bigger problem is you can't use an INSERT in an user-defined function.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-09 : 08:34:08

hmmmm... as always, people try way to hard to make things complicated.

why not something like:

CREATE FUNCTION FindBooks
(
@Author nvarchar(50), @Title nvarchar(50), etc ....
)

RETURNS @Books TABLE (
Author nvarchar(50),
Title nvarchar(50)
)

AS
BEGIN

set @Author = isnull(@Author, '%')
set @Title = isnull(@Title, '%')
... etc....

insert into @books
select author, titles
from
books
where
Author like @Author and
Title like @title and
..etc....
RETURN
END

In most of these cases, there is no need for dynamic SQL. Jay, I believe you can do INSERTS into table variables in a UDF --- may be wrong about that, however. But how else can you get data in your table variables to return them?

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-09 : 09:13:54

quote:
Jay, I believe you can do INSERTS into table variables in a UDF --- may be wrong about that, however. But how else can you get data in your table variables to return them?

Once again, I was a bit too quick on my post ... you are correct Jeff ...

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-09 : 09:33:41

That's Ok -- that was my first instinct as well !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources