- May 8, 2012
- 143
- 0
- 10,660
Hi everyone,
I have a question that I hope a SQL wizard can answer for me. I am querying a DB system to try and create a report of keywords that do not have any values present. There is a single table that keeps entries of each unique keyword value with a single numeric primary key. Each entry in that table also links to another table that holds the actual keyword values. That table would more or less be titled (don't want to give away anything proprietary) thi.keyword10 where the numeric identifier is the primary key of the keyword. I created a stored procedure with a cursor to grab all the desired keyword ID's. I then concatenate that value with the name of the table to produce the proper table name for that keyword. At this point I want to check whether any values exist in that table, and if not, print the name of the keyword/keyword table. Because the name of my table will change and it is stored in a variable, I cannot figure out how to use it as part of a query. I have tried simply executing a query with the variable attached (@var = 'Select * from ' +@keyvar + ';') but I cannot get it to operate as part of an IF EXISTS condition. I have posted my procedure here, I am wondering if I need to create that portion as another stored procedure that returns a value, or if there is a much easier way and I am just going about it the wrong way.
DECLARE @keyword_ID int, @keyname nvarchar(40), @keytable nvarchar(40),
@keyquery nvarchar(60);
DECLARE keyword_cursor CURSOR FOR
select distinct keytypenum, keytype from thi.keywordtable
where datatype != 2;
OPEN keyword_cursor
FETCH NEXT from keyword_cursor
into @keyword_ID, @keyname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @keytable = 'thi.keyword' + CAST(@keyword_ID as NVARCHAR);
SET @keyquery = 'select * from ' + CAST(@keytable as NVARCHAR);
IF NOT EXISTS(EXEC(@keyquery))
BEGIN
PRINT @keyname
END
PRINT @keytable
FETCH NEXT from keyword_cursor
into @keyword_id, @keyname
END
CLOSE keyword_cursor;
DEALLOCATE keyword_cursor;
Any help you can provide will be greatly appreciated!
PS, I do a decent amount of playing around in C# and SQL, if anyone knows of a fairly active and friendly community regarding those topics I would appreciate any suggestions!
I have a question that I hope a SQL wizard can answer for me. I am querying a DB system to try and create a report of keywords that do not have any values present. There is a single table that keeps entries of each unique keyword value with a single numeric primary key. Each entry in that table also links to another table that holds the actual keyword values. That table would more or less be titled (don't want to give away anything proprietary) thi.keyword10 where the numeric identifier is the primary key of the keyword. I created a stored procedure with a cursor to grab all the desired keyword ID's. I then concatenate that value with the name of the table to produce the proper table name for that keyword. At this point I want to check whether any values exist in that table, and if not, print the name of the keyword/keyword table. Because the name of my table will change and it is stored in a variable, I cannot figure out how to use it as part of a query. I have tried simply executing a query with the variable attached (@var = 'Select * from ' +@keyvar + ';') but I cannot get it to operate as part of an IF EXISTS condition. I have posted my procedure here, I am wondering if I need to create that portion as another stored procedure that returns a value, or if there is a much easier way and I am just going about it the wrong way.
DECLARE @keyword_ID int, @keyname nvarchar(40), @keytable nvarchar(40),
@keyquery nvarchar(60);
DECLARE keyword_cursor CURSOR FOR
select distinct keytypenum, keytype from thi.keywordtable
where datatype != 2;
OPEN keyword_cursor
FETCH NEXT from keyword_cursor
into @keyword_ID, @keyname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @keytable = 'thi.keyword' + CAST(@keyword_ID as NVARCHAR);
SET @keyquery = 'select * from ' + CAST(@keytable as NVARCHAR);
IF NOT EXISTS(EXEC(@keyquery))
BEGIN
PRINT @keyname
END
PRINT @keytable
FETCH NEXT from keyword_cursor
into @keyword_id, @keyname
END
CLOSE keyword_cursor;
DEALLOCATE keyword_cursor;
Any help you can provide will be greatly appreciated!
PS, I do a decent amount of playing around in C# and SQL, if anyone knows of a fairly active and friendly community regarding those topics I would appreciate any suggestions!
Facebook
Twitter