Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to create stored procedures in SQL Server 2005. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

. First, explain how to create a stored procedure:

CREATE PROCEDUER my_pro @ inputDate varchar, / / declare the input variable @ Result varchar / / declare the output variable AS declare @ variable1 varchar / / declare the varchar variable declare @ variable2 int / / declare the shaping variable BEGIN IF. (condition) BEGIN.... (executive content) END ELSE BEGIN. (execution) above END END is to create a simple stored procedure method.

Delete stored procedures

DROP PROCEDURE my_pro

Third, execute stored procedures

(1) execute stored procedures without parameters: EXECUTE my_pro (2) execute stored procedures with parameters: EXECUTE my_pro 'input variables' (1) execute stored procedures with parameters and return values (that is, output variables): declare @ Result varchar (1024) / declare output variables EXECUTE my_pro 'input variables' @ Result output / / Let the output values of the stored procedures be saved to @ Result and select @ Result as result// query results are returned

4. Below the example, you need to query multiple record results and return them with one return value.

For example: there is a table Test in which two fields are Test01 and Test02. The records with Test01 > 10000 are merged and the query Test02 results are merged into a single record to return. The returned result is: WSXEDCRFVTGB. Test01 Test02 10000 QAZ 10001 WSX 10002 EDC 10003 RFV 10004 TGB then, using stored procedures and cursors in stored procedures, you can perform a for loop to merge multiple records. This problem will be solved.

The specific stored procedures are as follows:

DROP PORCEDURE my_Cursor / / habitual, before creating, check to see if the stored procedure exists CREATE PROCEDURE my_Cursor @ id int, / / declare the input variable @ Result varchar (255) output / / declare the output variable, and note that there must be the output keyword Otherwise, default is input variable AS Declare city_cursor cursor for-declare cursor variable Select [Test01] from Test where Test01 > @ id-where @ id is the input variable Set @ Result =''- set variable @ result value Declare @ Field int- declare the variable Open city_cursor- that temporarily holds the queried Fetch next from city_cursor into open cursor Fetch next from city_cursor into @ Field-assign the actual Test01 to the variable Loop While (@ @ fetch_status=0)-start the loop BEGIN If @ Result =''BEGIN Select @ Result = Test02 from Test where Test01 = @ Field END ELSE BEGIN Select @ Result = @ Result +','+ Test02 from Test where Test01 = @ Field END Fetch next from city_cursor into @ Field-the next Test01 END Close city_cursor- closes the cursor Deallocate city_cursor- and releases the cursor reference GO (end) the following is the execution of Declare @ Result varchar (1024) EXECUTE my_pro 10000 The @ Result output-output keyword must be written If there are multiple input parameters, use the "," sign to distinguish between Select @ Result as result-query results

This is how to create stored procedures in SQL Server 2005. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.