Post reply

Dynamic CoulmnName in Select with assignment

  • April 23, 2012 at 2:54 am

    #392085

    Hello,

    Please edit the following line and explain what is the problem

    Declare @MyColumn nvarchar(50)

    set @AddItemVal = (SELECT @MyColumn from myTable Where Column2 = @Myval)

    the Error Msg:

    Conversion failed when converting the nvarchar value 'AddIt' to data type int.

    Notice: the 'AddIt' is a part of the original Column Name

    Regards

  • April 23, 2012 at 3:07 am

    #1477345

    Please post the complete query and the exact error message that you are receiving

    The query you have provided is not enough.

    You are declaring "@MyColumn" and using "@AddItemVal" and "@Myval" which have not been declared.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 23, 2012 at 3:08 am

    #1477347

    More information would be really, really helpful!

    What is the column type of ?AddItemVal?

    What is the column type of @MyVal?

    What is the column type of Column2

    Do have some sample data?

    Was this statement working previously and it's now stopped, or intermittent?

    We're happy to try and help but can only do so with complete, or near as complete information.

    Thank you.

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • April 23, 2012 at 3:19 am

    #1477350

    it works when I write the coulmn name instead of the variable

    the '@AddItemVal' is declared as nvarchar

    '@Myval' is nvarchar

    Column2 is nvarchar

    @MyColumn is nvarchar

    the problem happens only when I use @MyColumn instead of AddItem

    @MyColumn gets its value from a parameter

    AddItem is the Column Name in myTable

    All what I want to do is to make the coulmn name (AddItem) dynamic so I can use the same procedure to make query on any column of MyTable

  • April 23, 2012 at 3:38 am

    #1477356

    There is no easy way to do what you're looking to do. Too bad, I'd love to do this myself.

    The only way to query out columns dynamically would be to (you guessed it) use dynamic SQL. Assigning the result to your local variable then becomes problematic.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • April 23, 2012 at 3:49 am

    #1477360

    Build your query string dynamically, then execute it with sp_executesql. If you use an output parameter, you can get the result back to the scope of the calling batch.

    John

  • April 23, 2012 at 4:23 am

    #1477374

    can you please tell me how to do it ?

    I tried it many times but I got many errors

    thanks

  • April 23, 2012 at 4:28 am

    #1477376

    http://msdn.microsoft.com/en-us/library/ms188001.aspx

    If you want more detailed help, post the code and post the error messages.

    John

  • April 23, 2012 at 4:41 am

    #1477382

    John,

    That's exactly where I looked! And I can read and construct a working example too! 🙂

    DECLARE @sql nvarchar(500)

    DECLARE @AddItemVal INT

    DECLARE @MyColumn NVARCHAR(25)

    DECLARE @ParmDefinition nvarchar(500)

    CREATE TABLE #myTable (Col1 INT, Col2 INT, result INT)

    INSERT INTO #myTable

    SELECT 1, 2, 3

    UNION ALL SELECT 3, 4, 5

    UNION ALL SELECT 6, 7, 8

    SET @MyColumn = 'Col2'

    SET @sql = N'SELECT @AddItemVal = ' + @MyColumn + ' FROM #myTable WHERE Col2 = @MyVal'

    SET @ParmDefinition = N'@MyVal int, @AddItemVal nvarchar(25) OUTPUT';

    EXECUTE sp_executesql @sql, @ParmDefinition, @MyVal = 2, @AddItemVal = @AddItemVal OUTPUT;

    SELECT @AddItemVal;

    DROP TABLE #myTable

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • April 23, 2012 at 4:55 am

    #1477389

    Declare

    @table varchar(30),

    @col varchar(max),

    @query varchar(max)

    Set @table = 'tablename'

    Set @col = 'col1, col2, col3, col4'

    Set @query = 'Select '+@col+' From '+@table+';'

    Exec (@query)

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 23, 2012 at 5:21 am

    #1477412

    Thanks dwain.c

    But I cannot create a table, its a Web Application and many users use it at the same time

    and it will slow down my application too

  • April 23, 2012 at 5:24 am

    #1477414

    Dwain's not suggesting you create a table. He's creating a table purely so that he can test the code and show you how the code works since you did not post any table definitions.

    If you'd posted a table definition, he'd have used your table name. Since you didn't, he made one up.

    Replace #myTable with whatever your table name is and the example column names with whatever your columns are.

    Just note that this is not a good design and it's got some nasty security holes. Read up on SQL Injection before you put something like this into a web app.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 23, 2012 at 6:13 am

    #1477437

    Thanks guys for help I will test it out and give a reply soon

    GilaMonster,

    After our last discussion I started to read some about the SQL Injection

    I think using the dynamic SQLs with a plus mark (+) and qoutes is always dangrous So I have to learn more about the sql functions to avoid these risks correctly

    If you know a good books to learn SQL Server codes and the SQL Injections please tell me about then

    Thanks

  • April 23, 2012 at 6:14 am

    #1477439

    Try this: http://www.sommarskog.se/dynamic_sql.html

    John

  • April 23, 2012 at 6:21 am

    #1477441

    Thanks John it looks a great tutorial I will read it 🙂

Viewing 15 posts - 1 through 14 (of 14 total)

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