[MSSQL] 프로시저 생성 샘플코드

기본 프로시저

프로시저 생성

1. CREATE [or ALTER] PROCEDURE SP_SQUTY12PL
2. @year nvarchar(8)
3. , @season nvarchar(2)
4. , @returnVal nvarchar(2000) OUTPUT
5. AS
6. BEGIN
7. DECLARE
8. @sql NVARCHAR(2000)
9. set @sql = ' select * from squty02tt'
10. set @returnVal = @sql
11. END

프로시저 실행

1. DECLARE @year nvarchar(8)
2. DECLARE @season nvarchar(2)
3. DECLARE @returnVal nvarchar(2000)
4. set @year = '2012'
5. set @season = 'F'
6. exec dbo.SP_SQUTY12PL @year, @season, @returnVal OUTPUT
7. select @returnVal

동적쿼리를 이용한 프로시저

1. ALTER PROCEDURE SP_SQUTY12PL
2. @year nvarchar(8)
3. , @season nvarchar(2)
4. AS
5. BEGIN
6. DECLARE
7. @sql nvarchar(4000)
8. , @param nvarchar(400)
9. , @yy1 nvarchar(4)
10. , @yy2 nvarchar(4)
11. , @yy3 nvarchar(4)
12. set @param = '@p_year nvarchar(8)
13. , @p_season nvarchar(2)
14. '
15. set @sql = ' select top 20 * from squty02tt
16. where year = @p_year and season = @p_season
17. '
18. --set @returnVal = @sql
19. exec SP_EXECUTESQL @sql, @param, @p_year=@year, @p_season=@season
20. END

execute procedure

1. DECLARE @year nvarchar(8)
2. DECLARE @season nvarchar(2)
3. set @year = '2012'
4. set @season = 'F'
5. exec dbo.SP_SQUTY12PL @year, @season