sql生成（查询数据的存储过程）代码的存储过程

GO
/****** 对象:  StoredProcedure [dbo].[pro_GenerateProGet]    脚本日期: 08/03/2012 11:26:43 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pro_GenerateProGet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pro_GenerateProGet]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** PROCEDURE : [pro_GenerateProSet]
** DECRIPTION: 生成（查询数据的存储过程）代码的存储过程
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/03/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
CREATE procedure [dbo].[pro_GenerateProGet](
					@TbName NVARCHAR(200) ---表名
)
as
begin
DECLARE @TableName     NVARCHAR(200)  --表名
DECLARE @Parameters     VARCHAR(8000)  --所有参数
DECLARE @SelectFields     VARCHAR(8000) --插入列（无唯一键）
DECLARE @SelectConditions     VARCHAR(8000) --查询条件
SELECT @TableName = @TbName,@Parameters = '',@SelectConditions='',@SelectFields=''

if isnull(@TableName,'')=''
begin
	print '表名不能为空！'
	return 0
end 

----输出存储过程名
PRINT 'CREATE PROCEDURE dbo.pro_get_'+dbo.fun_get_UpperFirst(@tablename)+'('

----全部参数
SELECT @Parameters = @Parameters + dbo.fun_get_tabspace(1)+'@'+COLUMN_NAME+' '+( 
    CASE 
		WHEN DATA_TYPE='NVARCHAR' OR DATA_TYPE='VARCHAR' OR DATA_TYPE='CHAR'OR DATA_TYPE='NCHAR'
        THEN DATA_TYPE+ '('+CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(20))+')'
		when data_type='numeric' 
		then data_type+'('+CAST(numeric_precision AS NVARCHAR(20))+','+CAST(numeric_scale AS NVARCHAR(20))+')'
    ELSE
        DATA_TYPE
    END)+','+CHAR(10)   ---- CHAR(10)就是一个换行符
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKey

set @Parameters=LEFT(@Parameters,LEN(@Parameters)-2) ----去掉最后一个 ‘,’ 和 换行符

----查询字段
SELECT @SelectFields = @SelectFields + dbo.fun_get_tabspace(5)+'tb.'+COLUMN_NAME+' '+','+CHAR(10) -- CHAR(10)换行符
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKey
set @SelectFields=LEFT(@SelectFields,LEN(@SelectFields)-2)
---查询条件参数
SELECT @SelectConditions = @SelectConditions + dbo.fun_get_tabspace(1)+
(case when DATA_TYPE='numeric' then 'IF ISNULL(@'+COLUMN_NAME+',0)<>0 '
else 'IF ISNULL(@'+COLUMN_NAME+','''')<>'''' ' end)+CHAR(10)+dbo.fun_get_tabspace(1)+'begin'+CHAR(10)+dbo.fun_get_tabspace(2)+( 
    CASE 
		WHEN DATA_TYPE='NVARCHAR' OR DATA_TYPE='VARCHAR' OR DATA_TYPE='CHAR'OR DATA_TYPE='NCHAR'
        THEN  'set @sql_str=@sql_str+ '' AND '+COLUMN_NAME+' like ''''%'' + @'+COLUMN_NAME+' + ''%'''''''
    ELSE
		'set @condition_str = CAST(@'+COLUMN_NAME+' AS VARCHAR(20))'+CHAR(10)+dbo.fun_get_tabspace(2)+
        'set @sql_str=@sql_str+ '' AND tb.'+COLUMN_NAME+' like ''''%'' + @condition_str + ''%'''''''
    END)+CHAR(10)+dbo.fun_get_tabspace(1)+'end'+CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKey
--set @SelectConditions=LEFT(@SelectConditions,LEN(@SelectConditions)-2) ----去掉最后一个 ‘,’ 和 换行符
--set @SelectFields=Replace(@SelectConditions,'@','')

PRINT @Parameters
PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT dbo.fun_get_tabspace(1)+'DECLARE @sql_str varchar(4000),'
PRINT dbo.fun_get_tabspace(3)+'@condition_str varchar(20)'
PRINT dbo.fun_get_tabspace(1)+'SET @sql_str = ''select'
print @SelectFields
print dbo.fun_get_tabspace(5)+'from '+@TableName+' tb'
print dbo.fun_get_tabspace(5)+'where 1 = 1'+CHAR(10)+dbo.fun_get_tabspace(5)+''''
print @SelectConditions
print 'EXEC (@sql_str)'

PRINT 'END'
end
/*


exec pro_GenerateProSet 'classinfo'


exec pro_GenerateProGet 'stuinfo'



select * from INFORMATION_SCHEMA.COLUMNS


*/