Professional Web Applications Themes

pass a column ID as string to a stored procedure - Microsoft SQL / MS SQL Server

Hallo folks, I want to pass over to a stored procedure a column ID String (I mean the string name not an index integer number) . And in that procedure this column should be referenced in a select statement. Can I somehow p the some columns collection of a table after my colund ID string and then reference this in a select statement. something like select dbo.mytable.C1, dbo.mytable.columns.FindByID('C2') where .... Anton PS of course I can build a string including the complete select statement and execute it then inside the stored procedure, but this is certainly not the best performing ...

  1. #1

    Default pass a column ID as string to a stored procedure

    Hallo folks,

    I want to pass over to a stored procedure a column ID String (I mean the
    string name not an index integer number) . And in that procedure this column
    should be referenced in a select statement.

    Can I somehow p the some columns collection of a table after my colund
    ID string and then reference this in a select statement.

    something like

    select dbo.mytable.C1, dbo.mytable.columns.FindByID('C2') where ....


    Anton



    PS of course I can build a string including the complete select statement
    and execute it then inside the stored procedure, but this is certainly not
    the best performing way to do it!



    Anton Guest

  2. #2

    Default Re: pass a column ID as string to a stored procedure

    Hello Aaron thank you for your response
     

    when I wrote
     

    I of course thought of the string name and not an ordinal number! And after
    all it is my software client passing the ColumnName to the stored procedure
    so the column name (not ordinal ID) should be save enough.

    Your link does not explicitly provide that information of how to get a
    column Objekt when having the column name, can you give any other
    recommendation?


    thank you


    Anton


    Anton Guest

  3. #3

    Default Re: pass a column ID as string to a stored procedure

    > Your link does not explicitly provide that information of how to get a 

    What is a column object?

    What is the difference between:

    EXEC('SELECT * FROM '+tablename)

    and

    EXEC('SELECT '+columnname+' FROM table)

    ?


    Aaron Guest

  4. #4

    Default Re: pass a column ID as string to a stored procedure


    Thank you Aaron for your resopnse,


    I think there is something getting wrong here.
    I have solved my problem at the moment like you recommendet it here
     

    but then I wouldn't need a stored procedure because all it does is construct
    a string and have sql server anylyze it at runtime. So then I could build my
    select string clientside and simply send it to the server. Performance
    whyse just the same.

    What I meant was like in .NET programming. Some objekt (i.e.Table) is
    holding a collection of objekts (i.e. columns) if you search in that
    collection for the name (string) you will be returned the objekt you are
    searching for. And you can use it than as if you had specified it directly.
    In my case the select string fills more then a page of a screen and because
    of one stupid column name I have to create a string like above and sql
    server is then yzing the string each time the procedure is called what
    is performance whyse not really thrilling because you are not at all using
    the advantage of a stored procedure.
    So that is the difference between: 

    and lets say:

    select dbo.mytable.C1, dbo.mytable.columns.FindByID('C2') where ....

    because this would be completely precomiled and during runtime there would
    only be one string parsing done for columnname 'C2' . (even searching for
    the index (integer) of the column would help)

    Well at least such was my hope when I posted this.


    Sorry for making my point not clear enough at the first place but what I'd
    like to know is if there was a solution for that or not. And if I have to
    leave it whith exec('select...')


    Thank you


    Anton


    Anton Guest

  5. #5

    Default Re: pass a column ID as string to a stored procedure

    > What I meant was like in .NET programming. Some objekt (i.e.Table) is 

    SQL Server has no such construct.


    Aaron Guest

Similar Threads

  1. Using XML to pass data to an Oracle Stored Procedure
    By CF_DAWG in forum Coldfusion Database Access
    Replies: 4
    Last Post: July 21st, 02:52 PM
  2. Pass a Date to stored procedure
    By Mike Lopez in forum ASP.NET Web Services
    Replies: 1
    Last Post: January 25th, 10:06 PM
  3. Using query string to pass a value to a stored procedure parameter
    By Machelle Chandler in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 20th, 11:22 PM
  4. Need more help on how to pass parameters from stored procedure into trigger
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 1st, 09:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139