Post reply

Passing a Column name to a stored procedure.

  • December 2, 2003 at 9:50 am

    #83577

    Is there a way I could pass a column name to a stored procedure to be used in a select statement?

    Create PROCEDURE varcolSearch

    @colname varchar(30) = 'DefaultCol' AS

    Select T1.col1, t1.col2, t1.[@colname]

    From table as t1

    Where X

    Return

    Any insights would be greatly appreciated.

    Thanks,

    Chirs

  • December 2, 2003 at 10:13 am

    #484318

    You can create SQL string and than execute it in a stored procedure, like this:

    Create PROCEDURE varcolSearch

    @colname varchar(30) = 'DefaultCol' AS

    declare @U nvarchar(1000)

    set @U='Select T1.col1, t1.col2, t1.' + @colname + 'From table as t1 Where X '

    EXECUTE sp_executesql @U

    GO

  • December 2, 2003 at 2:54 pm

    #484375

    You can, but you probably shouldn't. Any solution is almost surely going to eliminate effective compiling and optimization.

    You're better off just writing a separate sp for each column. Swiss-Army-Knife stored procecures usually end up unreadable and unoptimizable.

  • December 3, 2003 at 12:37 am

    #484404

    Sure you can do this, but read http://www.algonet.se/~sommar/dynamic_sql.html first and decide then if you want to.

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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