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

    Unanswered: Stored Procedure w/Variable in select

    Hello again folks, I've come crying for some more help

    I have an sql query that runs fairly well and I'd like to make it a stored procedure.

    The only hickup here is that I need to be able to send a field name in the select statement as a variable.

    SQL Server does not allow this and I'm at a loss for the best way to handle it.

    Thx for any help

    CREATE PROCEDURE spReturnandScoring (@varKeyField as varchar(100),@varRegionID as varchar(10))
    AS
    Select
    @varKeyField,count(*) 'SurveysSent',Sum(SD.return_status) 'SurveysReturned',avg(alScoring.Score) 'SurveyScore'
    From
    tblSurveyData SD
    left join (Select Return_Key,cast(sum(response) as numeric)/cast(count(*) as numeric) as 'Score' from tblResponses RE group by return_key) alScoring on SD.objid = alScoring.Return_Key
    Where
    Region_ID=@varRegionID
    Group By
    @varKeyField
    Order By
    @varKeyField
    GO

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Of course you can convert it to dynamic SQL, but at a minimum 2 things will happen, - you'll have to give explicit SELECT permissions on tblSurveyData and tblResponses to all users that already have EXECUTE permission on this stored procedure, and, which is even bigger, - the performance of this query may go down the drain.

    My suggestion would be to see if there is not an infinite number of fields that can be passed to this procedure, and either have an IF...ELSE IF...END construct or have a separate procedure created for each field.

  3. #3
    Join Date
    Nov 2003
    Posts
    17

    Hmmm

    There are only about 11 options at the moment.
    The permissions I'm not TOO concerned about.
    and... What the hell is dynamic SQL?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What the hell is dynamic SQL?
    CREATE PROCEDURE spReturnandScoring (@varKeyField as varchar(100),@varRegionID as varchar(10))
    AS
    declare @SQL varchar(8000)
    set @SQL = '
    Select ' + @varKeyField + ',count(*) [SurveysSent],Sum(SD.return_status) [SurveysReturned],avg(alScoring.Score) [SurveyScore]
    From
    tblSurveyData SD
    left join (Select Return_Key,cast(sum(response) as numeric)/cast(count(*) as numeric) as [Score] from tblResponses RE group by return_key) alScoring on SD.objid = alScoring.Return_Key
    Where
    Region_ID=' + cast(@varRegionID as varchar(25)) + 'Group By ' +
    @varKeyField + 'Order By ' + @varKeyField
    exec (@SQL)
    GO

  5. #5
    Join Date
    Nov 2003
    Posts
    17

    Thx

    Ya know everytime I post here and get the answer back I feel like an idiot

    A very gratefull idiot however.

    Thanks VERY much for your help

    Rob

Posting Permissions

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