May 15, 2013 at 6:55 pm
DECLARE @SQL NVARCHAR(MAX)=''
DECLARE @LinkNumber INT
DECLARE @LinkCount INT=0
DECLARE @LinkTable NVARCHAR(101)=''
SELECT @LinkNumber= NoOfLinks FROM [ABC].[dbo].[MyTable] WHERE TableID=7
IF @LinkNumber>0
BEGIN
SET @LinkCount=1
WHILE (@LinkCount<=@LinkNumber)
BEGIN
SET @LinkTable=QUOTENAME('LinkTable'+CAST(@LinkCount AS NVARCHAR(2)))
SELECT @LinkTable= @LinkTable FROM [ABC].[dbo].[MyTable] WHERE TableID=7
SET @SQL='DELETE lnk
FROM ABC.dbo.'+@LinkTable+' lnk
JOIN #TempVID Temp
ON lnk.hubVID=Temp.TempVID'
SET @LinkCount=@LinkCount+1
SET @LinkTable=NULL
PRINT (@SQL)
END
END
The Code above generates a query like
DELETE lnk
FROM ABC.dbo.[LinkTable1] lnk
JOIN #TempVID Temp
ON lnk.hubVID=Temp.TempVID
DELETE lnk
FROM ABC.dbo.[LinkTable2] lnk
JOIN #TempVID Temp
ON lnk.hubVID=Temp.TempVID
But what i really wanted to do is have is have it generate it query where it has the Value of LinkTable1, LinkTable2..and so forth. I tried around a bunch of things and it doesn't work unless the column name is hardcoded. Any suggestions? thanks.
May 15, 2013 at 9:22 pm
Sorry confused. Can you show us what the actual results should be when you run this code?
May 16, 2013 at 12:33 am
Lynn Pettis (5/15/2013)
Sorry confused. Can you show us what the actual results should be when you run this code?
LinkTable1, LinkTable2, LinkTable3 ...are different fields in the table [ABC].[dbo].[MyTable]
So i am trying the query to get field values and not the field names , like
DELETE lnk
FROM ABC.dbo.[Charter] lnk
JOIN #TempVID Temp
ON lnk.hubVID=Temp.TempVID
Here Charter is the value of the field 'Linktable1' for TableID=7
May 16, 2013 at 1:41 am
I think your problem is this line:
SELECT @LinkTable= @LinkTable FROM [ABC].[dbo].[MyTable] WHERE TableID=7
Try changing it to
SELECT @LinkTable= LinkTable FROM [ABC].[dbo].[MyTable] WHERE TableID=7
Tom
May 16, 2013 at 3:24 am
If I understand correctly, you want the values out of a dynamic number of colums and use these values to create a new query.
It takes several steps to accomplish this. First you'll need to use a WHILE-loop to go through a dynamic number (your NoOfLinks) of columns. Each loop creates dynamic SQL to query the value from one of the columns. Within the loop you execute the generated dynamic SQL and stored the value in a table variable. When the WHILE-loop is finished you can generate the required dynamic SQL with the values in the table variable.
create table #MyTable (TableID INT, NoOfLinks INT, LinkTable1 nvarchar(50), LinkTable2 nvarchar(50), LinkTable3 nvarchar(50))
insert #MyTable
select 7, 3, '[FirstTable]', '[OtherTable]', '[LastTable]'
declare @command nvarchar(500)
declare @LinkNumber int
declare @LinkTable nvarchar(50)
declare @FieldValue table (Value nvarchar(50))
select @LinkNumber = NoOfLinks from #MyTable where TableID = 7
While @LinkNumber > 0
begin
SET @LinkTable = QUOTENAME('LinkTable'+CAST(@linknumber as nvarchar(2)))
set @command = 'select ' + @LinkTable + ' from #MyTable'
insert @FieldValue
exec sp_executesql @command
set @LinkNumber = @LinkNumber - 1
end
select 'DELETE lnk FROM ABC.dbo.' + Value + ' lnk JOIN #TempVID....' from @FieldValue
drop table #MyTable
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply