0

What is wrong with this statement, it works when I dont set the @label 

Declare
     @SourceTable varchar(255) = '[ServerXX\XX].[database].[dbo].[Manager]',
     @Label varchar(255)

     SET @Label = (SELECT Manager 
                FROM (
                    SELECT Manager, ROW_NUMBER() OVER (ORDER BY ManagerID) AS RowNum
                    FROM @SourceTable
                     ) AS MyDerivedTable 
                WHERE MyDerivedTable.RowNum = 2);

                print @Label

Error: but when I change @SourceTable with [ServerXX\XX].[database].[dbo].[Manager]...... it works but I want to use my @SourceTable

Must declare the table variable "@SourceTable".
shf301
  • 31,086
  • 2
  • 52
  • 86
er ser
  • 41
  • 1
  • 1
  • 6
  • 1
    What errors are being returned? Does the `SELECT` query return multiple values when you call it without setting `@Label`? – John Odom Aug 20 '15 at 20:09
  • First, this is tagged Oracle but appears to be SQL Server code. Define "works when I don't set the @label". Since assigning a value to @label seems to be the entire point of the block, I can't figure out what that means. Although I can guess, if you're getting an error, include the error. – Justin Cave Aug 20 '15 at 20:09
  • 2
    `DECLARE` both variables instead of combining? Syntax just looks funky to me. Could easily be wrong though, as I'm not 100% sure of all the SQL shorthand options. – JClaspill Aug 20 '15 at 20:10
  • 1
    Assuming that you are really using SQL Server, if your goal is to dynamically assemble and execute a SQL statement, you'd need to use sp_executesql https://msdn.microsoft.com/en-us/library/ms188001.aspx – Justin Cave Aug 20 '15 at 20:18
  • Does the SQL return 1 value when you run it separately? – devlin carnate Aug 20 '15 at 20:19

0 Answers0