none
rename field in table variable

Risposte

  • Correct.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/


    lunedì 16 luglio 2012 08:28
    Postatore
  • No Murali, table variable will be out of scope in Dynamic SQL batch. So my dynamic SQL solution won't work. I have overlooked that part. Either put all the declaring the table variable, populating variable and querying parts inside the dynamic SQL as Uri suggested or use temporary table instead of table variable and use dynamic SQL as in my code.

    I don't suggest dynamic SQL just to rename a column in this scenario. A better method will be to modify the table structure and query the table as I have suggested in the earlier post.


    Krishnakumar S

    • Contrassegnato come risposta arkiboys lunedì 16 luglio 2012 09:59
    lunedì 16 luglio 2012 08:47

Tutte le risposte

  • Once it is created it is not possible to alter the structure of the Table Variable by using ALTER TABLE statement. It is better to add another column in the declaration and use it whenever you want. 

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    lunedì 16 luglio 2012 07:35
    Postatore
  • Once it is created it is not possible to alter the structure of the Table Variable by using ALTER TABLE statement. It is better to add another column in the declaration and use it whenever you want. 

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    I see.
    Then do you know how I can change the field name of a select query?
    For example,
    The select query of a table is:
    select field1, fieldQrt, field5 from tblMain
    But I would like to return the same fields but to rename Qrt to a variable i.e.:
    set @Qrt = 3

    therefore,
    select field1, field + Qrt, field5 from tblMain
    Note that I am returning the same fields but only trying to rename one of the fields.
    How is it done please?
    Thanks

    lunedì 16 luglio 2012 07:46
  • You will have to assign three variables 

    select @v1=field1, @v2=fieldQrt, @v3=field5 from tblMain


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    lunedì 16 luglio 2012 08:00
    Postatore
  • You will have to assign three variables 

    select @v1=field1, @v2=fieldQrt, @v3=field5 from tblMain


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    That doe snot seem to work.
    See my sql below pls.

    declare @tblMain table (field1 float, field2 float, field3 float)

    declare @Qrt smallint
    set @Qrt = ?
    --Now I want to get the same fields but to only rename field2 to field?

    select Field1, 'field' + @Qrt = field2, field3 from @tblMain

    so the result is from field1, field2, field3 but only the field2 is renamed to field?

    Error I get is, incorrect syntax near '='

    Do you know what I am doing wrong pls?

    Thanks

    lunedì 16 luglio 2012 08:09
  • You will have to assign three variables 

    select @v1=field1, @v2=fieldQrt, @v3=field5 from tblMain


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    That doe snot seem to work.
    See my sql below pls.

    declare @tblMain table (field1 float, field2 float, field3 float)

    declare @Qrt smallint
    set @Qrt = ?
    --Now I want to get the same fields but to only rename field2 to field?

    select Field1, 'field' + @Qrt = field2, field3 from @tblMain

    so the result is from field1, field2, field3 but only the field2 is renamed to field?

    Error I get is, incorrect syntax near '='

    Do you know what I am doing wrong pls?

    Thanks


    The error message is because of the statement set @Qrt = ?... You have to assign an integer value here..

    Murali Krishnan

    lunedì 16 luglio 2012 08:12
  • You will have to assign three variables 

    select @v1=field1, @v2=fieldQrt, @v3=field5 from tblMain


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    That doe snot seem to work.
    See my sql below pls.

    declare @tblMain table (field1 float, field2 float, field3 float)

    declare @Qrt smallint
    set @Qrt = ?
    --Now I want to get the same fields but to only rename field2 to field?

    select Field1, 'field' + @Qrt = field2, field3 from @tblMain

    so the result is from field1, field2, field3 but only the field2 is renamed to field?

    Error I get is, incorrect syntax near '='

    Do you know what I am doing wrong pls?

    Thanks


    The error message is because of the statement set @Qrt = ?... You have to assign an integer value here..

    Murali Krishnan

    Even if i add an integer such as 3 it still gives the same error.

    Do you know why?
    Thanks

    lunedì 16 luglio 2012 08:14
  • select Field1, 'field' + @Qrt = field2, field3 from @tblMain

    This is the statement which is having the problem.. what are you trying to achieve here... The second column 'field' + @Qrt = field2 is not correct...


    Murali Krishnan

    lunedì 16 luglio 2012 08:16
  • select Field1, 'field' + @Qrt = field2, field3 from @tblMain

    This is the statement which is having the problem.. what are you trying to achieve here... The second column 'field' + @Qrt = field2 is not correct...


    Murali Krishnan

    I am basically trying to rename field2 based on a variable.
    The query below gives error:
    select Field1, 'field' + @Qrt = field2, field3 from @tblMain

    lunedì 16 luglio 2012 08:18
  • I think you have to use dynamic SQL for renaming the field name in this scenario as below:

    DECLARE @Qrt AS TINYINT
    DECLARE @SQL NVARCHAR(100)
    SET @Qrt = 3
    SET @SQL = 'select field1, field2 AS field' + CAST(@Qrt AS VARCHAR(2))+ ', field3 from tblMain'
    EXEC (@SQL)


    Krishnakumar S

    lunedì 16 luglio 2012 08:18
  • I think you have to use dynamic SQL for renaming the field name in this scenario as below:

    DECLARE @Qrt AS TINYINT
    DECLARE @SQL NVARCHAR(100)
    SET @Qrt = 3
    SET @SQL = 'select field1, field2 AS field' + CAST(@Qrt AS VARCHAR(2))+ ', field3 from tblMain'
    EXEC (@SQL)


    Krishnakumar S

    It is not that easy because as stated before, I am using a table variable i.e. @tblMain and so it can not be done the way you suggested.
    lunedì 16 luglio 2012 08:19
  • Then you have to use dynamic sql.. KrishnaKumar's solution should work fine...

    Murali Krishnan

    lunedì 16 luglio 2012 08:20
  • Then you have to use dynamic sql.. KrishnaKumar's solution should work fine...

    Murali Krishnan

    Can not do that because I am using a table variable i.e. @tblMain
    lunedì 16 luglio 2012 08:20
  • declare @Qrt char(1)
    set @Qrt = '?'
    exec('declare @tblMain table (field1 float, field2 float, field3 float)




    select Field1, ''field'' + '''+@Qrt+''' as field2, field3 from @tblMain')

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    lunedì 16 luglio 2012 08:20
    Postatore
  • declare @Qrt char(1)
    set @Qrt = '?'
    exec('declare @tblMain table (field1 float, field2 float, field3 float)




    select Field1, ''field'' + '''+@Qrt+''' as field2, field3 from @tblMain')

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    So,
    1- I have to build the @tblMain inside the exec
    2- Then populate the table variable inside the exec
    3- rename field, right ?
    lunedì 16 luglio 2012 08:24
  • Ooops..I overlooked. My approach do not work.

    Why you're trying to rename that column? To identify the quarter...? then instead of relying on column name to get a piece of data, is that easy to define your table to store the data and then query the table as below:

    DECLARE @Qrt AS TINYINT
    declare @tblMain table 
    (
    field1 float, 
    field2 float, 
    field3 float,
    Qtr TINYINT
    )
    ---
    -- remaining stuff
    -- populating the table variable..etc.
    ---
    SET @Qrt = 3
    SELECT field1, field2 AS [QrtData], field3 FROM tblMain
    WHERE Qtr = @Qrt

    I think this is the right approach and more efficient and maintainable.

    Krishnakumar S

    lunedì 16 luglio 2012 08:28
  • Correct.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/


    lunedì 16 luglio 2012 08:28
    Postatore
  • No Murali, table variable will be out of scope in Dynamic SQL batch. So my dynamic SQL solution won't work. I have overlooked that part. Either put all the declaring the table variable, populating variable and querying parts inside the dynamic SQL as Uri suggested or use temporary table instead of table variable and use dynamic SQL as in my code.

    I don't suggest dynamic SQL just to rename a column in this scenario. A better method will be to modify the table structure and query the table as I have suggested in the earlier post.


    Krishnakumar S

    • Contrassegnato come risposta arkiboys lunedì 16 luglio 2012 09:59
    lunedì 16 luglio 2012 08:47
  • Thanks
    lunedì 16 luglio 2012 09:18
  • No Murali, table variable will be out of scope in Dynamic SQL batch. So my dynamic SQL solution won't work. I have overlooked that part. Either put all the declaring the table variable, populating variable and querying parts inside the dynamic SQL as Uri suggested or use temporary table instead of table variable and use dynamic SQL as in my code.

    I don't suggest dynamic SQL just to rename a column in this scenario. A better method will be to modify the table structure and query the table as I have suggested in the earlier post.


    Yeah.. I learnt this today....Thanx....

    Murali Krishnan

    lunedì 16 luglio 2012 09:24
  • Thank you all.
    Temp table was the solution.
    lunedì 16 luglio 2012 09:59