SQLServer stored procedure Sample

SQLServerのストアドプロシージャのサンプルを備忘録として残しておきます。

ALTER PROCEDURE [dbo].[TESTPROC] 

DECLARE @TARGETCOL VARCHAR(100) = 'testvalue'
	   ,@T_SQL		VARCHAR(200) = 'sql ='

BEGIN TRY
begin
	DECLARE @T_CODE VARCHAR(50)
	DECLARE T_CUR CURSOR FOR
		SELECT product_cd  FROM dbo.product
		WHERE product_name = @TARGETCOL

	OPEN T_CUR
	FETCH NEXT FROM T_CUR INTO @T_CODE 

	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @T_SQL = @T_SQL + @T_CODE
			FETCH NEXT FROM T_CUR INTO @T_CODE
		END

	CLOSE T_CUR
	DEALLOCATE T_CUR
end

	PRINT @T_SQL
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS エラー番号
		  ,ERROR_SEVERITY() AS エラー重大度
		  ,ERROR_MESSAGE() AS エラーメッセージ
		  ,ERROR_LINE() AS エラー行
END CATCH