January 19, 2005 at 11:31 am
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)?
January 20, 2005 at 12:12 am
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
January 20, 2005 at 12:19 am
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
+44 (0)208 241 1762
Database change management for SQL Server
January 20, 2005 at 2:07 am
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),
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
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