Columns in SP

  • I have a table that has 30 columns. I then have a SP that needs 25 columns. So I do:

    CREATE PROCEDURE dbo.StudentSelectOne_sp

    (

    @StudentID int

    )

    AS

    SET NOCOUNT ON;

    SELECT col1,col2,col3,.....,col25

    FROM tblStudents

    WHERE StudentID=@StudentID

    GO

    Is there a shorter way to create the SP other than typing 25 column names (which can be error prone)?

  • If you search for "column gitter", you will find a few scripts that will do what you want. Given a tablename a series of column lists is generated (columns, declarations, variables, assignments) in Query Analyzer. Copy and paste the appropriate result in your sp.

    The scripts can be changed to allow for more column manipulations.

     

    Erik

     

  • In Query Analyzer press F8 for the object browser.

    Drill down into the database of choice until you find the table you want to work with.

    Right click, you'll be able to script a select from the table and you can paste this into your procedure, deleting those columns you don't want to select.

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    +44 (0)208 241 1762

    Database change management for SQL Server

     

     

     

  • Create

    PROCEDURE ReturnColumns

    /******************/

    --Created by Muralikrishna

    -- @ num holds the number of columns to be selected

    -- @ tabname holds the table name

    /******************/

    (

    @num int,@tabname sysname)

    AS

    BEGIN

    DECLARE

    @ColList varchar(1000),

    @SQL

    varchar(2000)

    SELECT

    @ColList=COALESCE(@ColList + ', ', '') + column_name

    FROM INFORMATION_SCHEMA.Columns

    WHERE

     table_name = @tabname and ordinal_position <= @num

    SET @SQL='Select ' + @ColList + ' from '+ @tabname

    Print

    @SQL

    Exec

    (@SQL)

    END

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

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