Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    65

    Unanswered: How to eval() a variable to get a column name?

    OK.. I've got a stored procedure I'm writing, which accepts an argument called @statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

    select * from table1 where @statfield = @value

    I want to do basically an eval(@statfield) so if @statfield is "key_id", then the select statement comes out:

    select * from table1 where key_id = @value

    How can I do this?

    Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Re: How to eval() a variable to get a column name?

    Originally posted by MDesigner
    OK.. I've got a stored procedure I'm writing, which accepts an argument called @statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

    select * from table1 where @statfield = @value

    I want to do basically an eval(@statfield) so if @statfield is "key_id", then the select statement comes out:

    select * from table1 where key_id = @value

    How can I do this?

    Thanks!
    One way would be to build dynamic sql and execute it

    I copy/pasted the following from SQL Server help


    Building Statements at Run Time

    DECLARE @SQLString NVARCHAR(500)

    /* Set column list. CHAR(13) is a carriage return, line feed.*/
    SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)

    /* Set FROM clause with carriage return, line feed. */
    SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)

    /* Set WHERE clause. */
    SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''

    EXEC sp_executesql @SQLString

  3. #3
    Join Date
    Jan 2004
    Posts
    65
    One problem:

    my sql statement is:

    select distinct @stat = packing_shipping from cp_elements where campaign_id = 10

    however, if I use execlsql to execute that, @stat is in some kind of local scope...and is asking to be declared, even though it already is.

    How do I get my @stat return value?? I can't do

    select @stat = exec sp_executesql @sql

    nor this:

    exec @stat = sp_executesql @sql

    help!

  4. #4
    Join Date
    Dec 2003
    Posts
    31
    declare @stat <data type>
    exec sp_executesql @sql, N'@stat <data type> out', @stat out

    print @stat

  5. #5
    Join Date
    Jan 2004
    Posts
    65
    Hm, that didn't work for some reason..

    declare @stat int

    .....

    set @sql = N'select distinct ' + @statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @campaign_id)
    exec sp_executesql @sql, N'@stat int out', @stat out
    set @rc = @@rowcount
    select @stat

    @stat shows up as NULL for some reason. did I do something wrong here?

  6. #6
    Join Date
    Jan 2004
    Posts
    65
    nevermind. altered the SQL and it worked:

    set @sql = N'select distinct @stat = ' + @statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @campaign_id)

Posting Permissions

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