How do I reference columns in a table variable?
-
Disregard.
-
Why did you say to disregard? Did you figure out how to do this?
-
I was overcomplicating it. I ended up using a user-defined function that returned a table, and calling that from within a cursor. I have an Oracle background, and so I didn't know that you can select directly from a UDF in SQL Server just as if it were a table.
-
That's interesting... I did not know that could be done either. Any chance you could provide an example? I use cursors all the time and would love to loop through tables and/or server names and if it means creating a UDF to make that work then that'd be great. Thanks for replying btw!
-
Create a function that returns a table like so:
*********************
CREATE FUNCTION myuser.wildcard_search (@search_string varchar(50))
RETURNS @Result TABLE
(
title varchar(200),
author_name varchar(200)
 
BEGIN
declare @title varchar(80)
declare @author varchar(200)
declare @publisher varchar(200)
declare titles cursor for
select title
from titles
where title like '%' + @search_string + '%'
open titles
fetch next from titles into @title
while (@@fetch_status=0)
begin
-- get the author and publisher name here
-- insert into @result
-- <some select statement here >
fetch next from titles into @title
end
close titles
deallocate titles
return
END
*********************
Now you can treat the above function just like a regular table, at least for SELECTing purposes:
select distinct author_name
from wildcard_search('the')
Brings back all of the data associated with a title containing 'the', 'then', 'there','father', etc. (providing you complete the implementation.)
-
Ignore that emoticon, I don't know how that got in there. Supposed to be another right-paren.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply