August 21, 2008 at 10:23 am
Is there a way I can return a recordset via a SP which contains the fieldnames that hold a particular string?
my table would be set as:
Date Field1 Field2 field3 field4
12/08/2008 Tony Jim Tony Sally
13/08/2008 Sally Tony George Tony
What I'd like to do is something like return fields that contain "Tony" on the 12/08/2008"
This would return Field1,Field3
I can do this using VB6 to manipulate the recordset, but I'd imagine it would be faster using a SP to do the work
August 22, 2008 at 4:30 am
I was given the following code a while ago by Karl, but it just produces an error
create table ##field_name (field varchar(128))
--insert into temp table the row we want so that we don't have to select from the table later on.
--need to perform a select * into because we don't know the columns ahead of time.
select *
into ##my_table
from my_table
where date = '25/12/2007'
declare @column varchar(128)
--cursor through all of the columns in our table
declare col_cursor cursor
for
select column_name
from information_schema.columns
where table_name = 'my_table'
open col_cursor
fetch next from col_cursor into @column
while @@fetch_status = 0
begin
--if the current column contains 'jim' then let's insert into our ##field_name table
exec('if exists (select 1 from ##my_table where [' + @column '] = ''jim'') insert into ##field_name(field) values(''' + @column + ''')')
fetch next from col_cursor into @column
end
close col_cursor
deallocate col_cursor
--select all of our field names
select * from ##field_name
drop table ##field_name
drop table ##my_table
The error I get when run in the QA is
Incorrect syntax near '] = 'jim') insert into ##field_name(field) values(''.
Has anyone any ideas?
August 22, 2008 at 5:38 am
Your missing a + sign after the first reference to the @column variable
exec('if exists (select 1 from ##my_table where [' + @column + '] = ''jim'') insert into ##field_name(field) values(''' + @column + ''')')
August 22, 2008 at 5:48 am
Thanks Clive, it now works, I've been looking at that code for ages and never spotted it !!
August 22, 2008 at 5:54 am
Ha! I've missed the obviousl like that too many times and spent an hour to find the problem 🙂
August 22, 2008 at 6:17 am
Clive, can I impose on your knowledge once more? in the line
exec('if exists (select 1 from ##my_table where [' + @column + '] = ''jim'') insert into ##field_name(field) values(''' + @column + ''')')
I'd like to use a variable instead of 'jim' I seem to get errors no matter how I format the line
August 22, 2008 at 6:19 am
mick burden (8/22/2008)
Clive, can I impose on your knowledge once more? in the lineexec('if exists (select 1 from ##my_table where [' + @column + '] = ''jim'') insert into ##field_name(field) values(''' + @column + ''')')
I'd like to use a variable instead of 'jim' I seem to get errors no matter how I format the line
Sure thing...
exec('if exists (select 1 from ##my_table where [' + @column + '] = ''' + @Name +''') insert into ##field_name(field) values(''' + @column + ''')')
Should do the trick.
August 22, 2008 at 6:21 am
Just the trick, many thanks Clive
August 22, 2008 at 6:49 am
one last thing Clive, I've put the whole thing into a SP to be read by a VB6 program, the thing is when I try to read the data it tells me the object is closed, any ideas?
August 22, 2008 at 7:05 am
When you run the stored procedure from Query Analyser, does it return the correct recordset?
It's been about 4 years since I've used VB so I'm not too sure about the front end issues these days..
August 22, 2008 at 7:07 am
yes it does, that's why I was puzzled when it threw up errors in the vb code
August 22, 2008 at 7:25 am
I've a sneaky feeling the SP is erroring before the final select statement, but I can't find out where, the stored procedure is as follows
CREATE PROCEDURE spGetareas_byName_and_Date @Name as varchar(100),@Date as varchar(20)
AS
create table ##field_name (field varchar(128))
select * into ##my_table from TI_Work_New where date1 = @Date
declare @column varchar(128)
--cursor through all of the columns in our table
declare col_cursor cursor
for
select column_name from information_schema.columns where table_name = 'TI_Work_New'
open col_cursor
fetch next from col_cursor into @column
while @@fetch_status = 0
begin
exec('if exists (select 1 from ##my_table where [' + @column + '] like ''' + @Name +''') insert into ##field_name(field) values(''' + @column + ''')')
fetch next from col_cursor into @column
end
close col_cursor
deallocate col_cursor
select * from ##field_name
drop table ##field_name
drop table ##my_table
GO
Unless of course it's a stupid mistake on my part
August 28, 2008 at 3:02 am
I was right, it was a stupid mistake on my part. I forgot to use SET NOCOUNT ON
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply