This script will get the schema for a stored procedure or function from the system tables.
Set the @Routine variable to the value of the object whose schema you are looking for.
Anthony-peiris,
2010-09-09 (first published: 2010-09-04)
This script will get the schema for a stored procedure or function from the system tables.
Set the @Routine variable to the value of the object whose schema you are looking for.
CREATE Function [dbo].[ftGetRoutineSchema](@RoutineName varchar(200)) returns table as return --declare @routineName varchar(100);select @routineName='ftGetBom4Excel' SELECT ColumnName=Case Is_Result When 'YES' then '@RC' else Parameter_Name end ,DataType= case Data_Type When 'DECIMAL' then 'Decimal('+convert(varchar,Numeric_precision)+','+Convert(varchar,Numeric_scale)+')' When 'numeric' then 'Decimal('+convert(varchar,Numeric_precision)+','+Convert(varchar,Numeric_scale)+')' when 'varchar' then 'Varchar('+Convert(varchar,Character_maximum_length)+')' ELSE dATA_TYPE end ,ColumnOrder=Ordinal_Position ,Direction =Case Parameter_Mode when 'INOUT' then 'Out' else Parameter_Mode end FROM --INFORMATION_SCHEMA.ROUTINE_cOLUMNS Information_schema.Parameters --WHERE TABLE_NAME=@routineName --order by columnorder Where Specific_name=@ROUTINEnAME
Tim Chapman discusses using table valued functions with the APPLY function.
Continuing with his series on SQL Server 2005 from the developer's perspective, M. Choirul Amri examines the ROW_NUMBER function and gives a few examples of how you can use this in your application.
2006-05-11
13,438 reads
There are many changes in SQL Server 2005, especially for the SQL Server developer. New options, features, and changes in the way you program. New author M. Choirul Amri brings us the first in a series on the changes from the point of the developer.
2006-03-29
13,924 reads
This article will acquaint you with aggregate functions such as MIN, MAX, COUNT, and AVG, which easily let you perform tasks that you may have thought needed extensive programming codes to accomplish.
Many people have posed the question of how to generate sequential numbers for some report when the underlying data has no numbering system. The solutions have always been complicated derived or temporary tables and are difficult to implement. SQL Server 2005, however, brings us the ROW_NUMBER() function and author Michael Coles shows us how this can be used to easily add sequential numbering to your results.
2006-02-14
18,789 reads