SQL Server2005 过程存储的用法

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	对字典类型和字典类型详细信息进行映射
-- =============================================
ALTER PROCEDURE [dbo].[DicSubTypeAllInfo]
( @DicId varchar(3) = NULL,
  @DicSubId varchar(3) = NULL
)

AS
BEGIN
    DECLARE @StrSQL VARCHAR(2000)
    SET @StrSQL = 'SELECT WZOU_DictionaryType.DicTypeId, WZOU_DictionaryType.DicTypeName AS TypeName, WZOU_DictionaryDetail.DicSubTypeId, WZOU_DictionaryDetail.DicSubTypeName FROM WZOU_DictionaryType, WZOU_DictionaryDetail WHERE WZOU_DictionaryType.DicTypeId = WZOU_DictionaryDetail.DicTypeId'

    IF (@DicId is not NULL)
        BEGIN
        SET @StrSQL = @StrSQL + ' AND WZOU_DictionaryDetail.DicTypeId = ' + @DicId + ''
        END

    IF (@DicSubId is not NULL)
        BEGIN
        SET @StrSQL = @StrSQL + ' AND WZOU_DictionaryDetail.DicSubTypeId = ' + @DicSubId + ''
        END

    PRINT @StrSQL
    EXEC(@StrSQL)			
END

测试 
EXECUTE DicSubTypeAllInfo '001','001'