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 ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* PROCEDURE : [pro_GenerateProSet]** DECRIPTION: 生成（查询数据的存储过程）代码的存储过程** VERSION      AUTH          DATE          Defect NoDESC** --------  ------------  ------------  -----------------   ------------------------------** V000.0.1    pukuimin     08/03/2012新建程序** --------  ------------  ------------  -----------------   -------------------------------*******************************************************/CREATE procedure [dbo].[pro_GenerateProGet](@TbName NVARCHAR(200) ---表名)asbeginDECLARE @TableName     NVARCHAR(200)  --表名DECLARE @Parameters     VARCHAR(8000)  --所有参数DECLARE @SelectFields     VARCHAR(8000) --插入列（无唯一键）DECLARE @SelectConditions     VARCHAR(8000) --查询条件SELECT @TableName = @TbName,@Parameters = '',@SelectConditions='',@SelectFields=''if isnull(@TableName,'')=''beginprint '表名不能为空！'return 0end ----输出存储过程名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.COLUMNSWHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKeyset @Parameters=LEFT(@Parameters,LEN(@Parameters)-2) ----去掉最后一个 ‘,’ 和 换行符----查询字段SELECT @SelectFields = @SelectFields + dbo.fun_get_tabspace(5)+'tb.'+COLUMN_NAME+' '+','+CHAR(10) -- CHAR(10)换行符FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKeyset @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.COLUMNSWHERE TABLE_NAME = @TableName --AND COLUMN_NAME != @PrimaryKey--set @SelectConditions=LEFT(@SelectConditions,LEN(@SelectConditions)-2) ----去掉最后一个 ‘,’ 和 换行符--set @SelectFields=Replace(@SelectConditions,'@','')PRINT @ParametersPRINT ')'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 @SelectFieldsprint 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 @SelectConditionsprint 'EXEC (@sql_str)'PRINT 'END'end/*exec pro_GenerateProSet 'classinfo'exec pro_GenerateProGet 'stuinfo'select * from INFORMATION_SCHEMA.COLUMNS*/