none
Dynamic query RRS feed

  • Question

  • Hello

    I have a problem with writing a query.Let me give an example:

    Table:

    ColA  ,    ColB  ,   ColC   ,   Col1  ,   Col2  ,   Col3   ,   Col4   ,    Col5

    Ok.I must write a SP and it gets a parameter,say @param. if @param=1 then in the select statement I will select Col1,if @param=2 then I will select Col2 and so on.

     

    How can I do this?

     

    Thanks.

     

    Sunday, September 17, 2006 3:58 PM

Answers

  • Your variable @q must be of type ntext, nchar, nvarchar to be used with procedure sp_executesql.  Change your variable from the current varchar to nvarchar.
    Monday, September 18, 2006 12:27 PM

All replies

  • take note of this. this will provide you all the info you need

    use northwind
    select * from syscolumns where id =(
    select id from sysobjects where name='employees')

    here's the Sp:


    create proc dynamicselect(
    @columnid int
    )as
    declare @dynamicsql nvarchar(200)
    select @dynamicsql ='select '+ name+ ' from employees'
    from
    (
    select * from syscolumns where id =(
    select id from sysobjects where name='employees')
    )as a

    where colid=@columnid
    exec sp_executesql @dynamicsql

    go
     execute dynamicselect 2
     execute dynamicselect 1

    Sunday, September 17, 2006 4:48 PM
  • Joey gives an answer how. You could also do:

    declare @query varchar(max)
    set @query = 'select col ' + cast(@param as int) + ' from table'

    Or if the column names aren't actually numberd, use a CASE expression to pick the columns. 

    The question I have is why do this?  If all columns are only useful one at a time, this is probably a design issue.  If this is to support some user interface issue, the user interface ought to be redesigned.

    I would also suggest that you might want to think about just having a procedure per column configuration that you end up with.  This will be better all around for you, particularly in performance and security.  It will take a bit more coding of course

    Sunday, September 17, 2006 8:22 PM
    Moderator
  • -------------------------------------------------------------------------------------------------------------------------------------

    CREATE PROCEDURE dbo.dynsql

    @param int

    AS

    BEGIN

             DECLARE @q varchar(max)

           select @q = 'SELECT sym_no'+CAST(@param AS nvarchar(2)) + ' FROM TABLE'

          exec sp_executesql @q

    END

     

    execute dynsql 2

    -----------------------------------------------------------------------------------------------------------------------------------------

    I tried this but I get an error: "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    What's wrong with this..?

    Monday, September 18, 2006 9:29 AM
  • Your variable @q must be of type ntext, nchar, nvarchar to be used with procedure sp_executesql.  Change your variable from the current varchar to nvarchar.
    Monday, September 18, 2006 12:27 PM
  • D'oh!  I forgot that :)
    Monday, September 18, 2006 1:43 PM
    Moderator