SQL Question: Using VARCHAR variable in a query

chugot9218

Honorable
May 8, 2012
143
0
10,660
9
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!
 

chugot9218

Honorable
May 8, 2012
143
0
10,660
9
Actually after posting that I had thought of that too, I am still stuck in the conundrum of not being able to use my table name as a variable however, unless I am missing something.
 
Thread starter Similar threads Forum Replies Date
M Apps General Discussion 1
S Apps General Discussion 1
T Apps General Discussion 10
T Apps General Discussion 2
H Apps General Discussion 1
B Apps General Discussion 2
L Apps General Discussion 1
N Apps General Discussion 5
T Apps General Discussion 1
N Apps General Discussion 3
M Apps General Discussion 3
P Apps General Discussion 2
appletatoes Apps General Discussion 3
M Apps General Discussion 1
B Apps General Discussion 7
tjones9163 Apps General Discussion 2
S Apps General Discussion 3
T Apps General Discussion 3
M Apps General Discussion 1
J Apps General Discussion 1

ASK THE COMMUNITY