Query result

  • 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.

  • Sorry confused. Can you show us what the actual results should be when you run this code?

  • 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

  • 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

  • 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

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply