Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Lightbulb Unanswered: Passing table Column name as parameter to a stored procedure

    I want to run a Stored Procedure which takes Column name( table column names ) as input, use some aggregate function and return the desired output.

    I have tried passing a single column name as input parameter and also the entire SQL statement as input parameter. but i am not able to capture and return the output value

    I have also tried using Table data type but fail to capture the value. I dont want to use Temporary table.

    The syntax i tried is some thing like this

    Declare @stmt nvarchar(100)
    Declare @AcctCode Char(8)
    Declare @rtVal numeric(18,5)

    Set @AcctCode = 'An_Sales'
    Set @stmt = 'Select AVG(' + @ACCTCODE + ') From T_Comp_Profile'
    Exec sp_executesql @rtval = @stmt

    And also

    Declare @AcctCode Char(8)
    Declare @Ssql NVarchar(100)
    Declare @rtVal numeric (18,5)
    Set @AcctCode = 'An_Sales'

    Set @Ssql = 'Select ' +@rtval + '=AVG(an_sales) into From T_Comp_Profile'
    Exec SP_ExecuteSql @Ssql
    print @rtval



    Pls help me in this regard

    Ramanbir Singh

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    try something like....

    Declare @mystmt nvarchar(100)
    Declare @AcctCode Char(8)
    Declare @rtVal numeric(18,5)

    Set @AcctCode = 'An_Sales'
    Set @mystmt = 'Select AVG(' + @ACCTCODE + ') From T_Comp_Profile'
    Exec sp_executesql @stmt= @mystmt

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Dear Rockslide

    U have wriiten me with the following code

    Declare @mystmt nvarchar(100)
    Declare @AcctCode Char(8)
    Declare @rtVal numeric(18,5)

    Set @AcctCode = 'An_Sales'
    Set @mystmt = 'Select AVG(' + @ACCTCODE + ') From T_Comp_Profile'
    Exec sp_executesql @stmt= @mystmt


    The stt "Exec sp_executesql @mystmt" this returns a data set, so it is not going to be stored in a variable like u specified i.e
    Exec sp_executesql @stmt= @mystmt

    because when we print the value of @stmt using "Print @stmt" it returns nothing also we cant use a table data type here in place of @stmt

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    hi rjaj

    I think I am a little confused.

    sp_executesql takes (basically) 2 different parameters, see the syntax below.

    sp_executesql [@stmt =] stmt
    [
    {, [@params =] N'@parameter_name data_type [,...n]' }
    {, [@param1 =] 'value1' [,...n] }
    ]

    When we say

    exec sp_executesql @stmt=@mystmt

    we are effectively saying

    exec sp_executesql @stmt = 'Select AVG(' + @ACCTCODE + ') From T_Comp_Profile'

    if we do nothing else with the returned results they will be outputed.

    if you want the results to return to a parameter then you would need to do something like

    select @results = sp_executesql @stmt = 'Select AVG(' + @ACCTCODE + ') From T_Comp_Profile'

    at a guess (the line above hasn't been tested, in theory I think it should work).

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    create table #tbl ([output] int null)
    insert #tbl Exec (@stmt)

  7. #7
    Join Date
    Nov 2003
    Posts
    5

    Passing table Column name as parameter to a stored procedure

    Originally posted by ms_sql_dba
    create table #tbl ([output] int null)
    insert #tbl Exec (@stmt)
    Using temporary tables work
    but i dont want to use a temporary table
    Is there any other way for that

Posting Permissions

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