存储过程基础

9/1/2015来源:SQL技巧人气:1980

存储过程基础

  这一篇要总结的是存储过程,包括存储过程有哪几种,如何创建,以及最后如何调用存储过程。所以分为以下几个方面进行总结。

1,不带参数的存储过程

2,带输入参数的存储过程

3,带输入和输出参数的存储过程

4,带返回值的存储过程

不带参数的存储过程

例如,以下存储过程返回Employees表中所有职员的记录。

存储过程代码:

USE TSQLFundamentals2008;GOIF OBJECT_ID('usp_PRocDemoNoParam','P') IS NOT NULL DROP PROC usp_ProcDemoNoParam;GO-- 1,不带参数CREATE PROC usp_ProcDemoNoParamASBEGIN    SELECT * FROM HR.Employees;ENDGO

调用代码:

USE TSQLFundamentals2008;GO-- 1,不带参数存储过程的调用EXEC usp_ProcDemoNoParam;

结果:

QQ9a2oN4H2QiAQCASSjwwkPdUtrfY5nvbxF4b06CsQ5CKH9a9vAcOC2wRTj2U5Q3oqxnPwqa2+9nPnzp2HH3740gH3tfxbFOnxWeRTnvSMWyawZsZeCAQCgUDykTjS035XS7+lVX1p654GzEpPUsKQnpbxHBw0pKcotsXRRx55pLh0dADp8ekwbIGHOZ35CKQHAoFAIJlLHOk5duzY0aOF9aq/qbC4VYGW95Ceg21xcLT9ztrt27cfeuiha9eu8T/AeMshPbcCVrN617d6yzD1MPZCIBAIBJKPYJd142D1/azqJworqUlPcXRbHL1x48bR4ihDeiYVi+uMYi8EAoFAipkISI9xsHpI+UUvetHRoqikIj31jxM2N/WWuoXnW8UZbC8EAoFAIPkISE9PsYriuLK4/hPZC4FAIBDIvgpIz/KawF4IBAKBQGaQ3EnPFgAAAACAfUd12Z8v6YFAIBAIBJKDtPc6ONJzDgAAAAAAQCZaSnMukPQcBwAAAAAAkAad0kSQnklvsE1XuSAd5ocsq2VpKw4ZujdDk0UAcUkTcuMynPRMd4Ot1WzBm3zL6gCr90lbcZKhezM0WYQgLmmK9LiA9CSnA6zeJ23FSYbuzdBkEYK4pCnS4wLSk5wOsHqftBUnGbo3Q5NFCOKSpkiPS2ak5+rpI4dPl7E69J0lXXyev3hSKaVOXVhewxBtadn32Alyb5rdyWtyuGl8SfTApeKCcMwZl1i3LxeIvSY98Y7eaZiJHU4ez58/pU5c7LVrdqt7YlS9sf5d2sOCZDL3Nt1pR5kgoKGj3joS6ATfp+iZE8WFF4RjorjEuh2kZ6ArYwWkR77VQdqC9KTm3rECSSDp8WkC0jONzLrSg3DsGJdYt4P0cK68evpIvZX4obNXK2edOHVYKaWOnDl/tn5zRT+urCWKrgaldiA91fp8hWqVvjxzqPq31UQ118FnTxol7XN9Vrj2Tt8JKKvPn1KNJ7uL+0rhE2cXtZrS9oqpRtMNrDWJeb0qVEZzb3fkxEW9Oxm9q7nbZRQ2E6Ld/bRR7DSqjccrF08qdfJ8ZcLFk003CzX5ujefuJpQPd/NOeZY0FyKzhkdF9PbToK6yPVGhGOauFDzIzHzWkfYqOnD2f13sKRPeq6cPax13MpNLfvpaJB2vPLOyfNN6rxy9nATgwsndiE9zhHtBlBLDpq4dlEnz/VZ4dq7VCd272jU7l3Y6kH3XxbwqlAZyb3doDMH4/Vb+ni8ef6UOnT2KlOY6jldPVajTs/sOBZ3Z81rsqK0cjUhej6bcywfonMOjYvhyS5Bcb0R4ZgqLq5LCbc7R7iomcPZ/ne4/smTHp0YVhzwgrOqbPdacz52E+gAHegVoyv2WoW73O071y3cDV3T3ukvNeLmuaWtHjIrL+FVoTKOey84nYHsJ1blRGfzn07E1OyZN69fPKlOXbh+68KJdslniMk3PfmE6fmxYwGdc3BcdNLj86fbGxGO0ePCuPRm2LjuGc726B4sIkiPSeuYiZMfDDephBuoQ0086SuAiyeVstY87Badc7npf4znPUfxvM+9i1o9dFae26tCZRz3hrCWkMLM6f6Y1j3zZk13Lp4ccMM0bHKNID3sWEDnHDcuvb0R4Rg9LoxLQ46EDGfq3wGSPOnRF8T0662bVCJufOG7vVWeObTD7S1tkZxcmnOXoPhz6dTp2LtUJ2bmuUWtHnr/ZW6vCpWR3Lvb7a2QnkNmBnup/Pwp1fMYH2cymU9CSA+Rc/wWoXMO64pWb+T8iXBMHxfXpYTbnSPhw/kCdSNskKRPeq5rjz06t9LtRHzoSLu2ZhWojx/aYaWne5CqbcJ8tKrSU6NlzLlc6jTtXaoT++a5pa32DzldDVerub0qVEZzbzfo3AUe/4PMTh+rx6zdkPVANP3k462+R5g5k6+eJp7BJDWher6bc5zswRkC4eLSQ3r43ohwTBMXan4kZl7rCBu1bB9kDhbDfQvpIFNkWS1LW3GyX+4N+nGg/TJ5fwRxSVOkxwWkZ6/CmYPVsrQVJ/vj3gsnAi8K98fk/RLEJU2RHpc9Ij37ogOs3idtxUmG7s3QZBGCuKQp0uMykPScmwatKyeqX4oO80OW1bK0FYcM3ZuhySKAuKQJuXEZSHomxXa7nboJETrMD1lWy9JWHDJ0b4YmiwDikiaExmUg6Zl69UkptfgrBAKBQCCQPZOBpGc7DarKP7g0lFITGZgypgvrFJClrThk6N4MTRYBxCVNCI3LTqRndP7VVq6UWpb0CA3njpBltSxtxSFD92ZosgggLmlCaFwSJT3LMp4PYqVHAmRpKw4ZujdDk0UAcUkTQuOSKOnBSs8ikGW1LG3FIUP3ZmiyCCAuaUJoXEYnPUG/f7rbSs89J9XJeyYmPd6Vns161f3OtirKGWM1PbhOrBmeiNWMtnqUam3NwHXHy6I5sFpv5lFcBrzurTzZdYLNerUnzutL4mWhmd12nOZQ15Nad7SdzuMeukJ3gLkfMTW7haNqnrMwc7oOJi7DWolRmwgr03pzmLAnn7iQudbtsc4ICm03Tu3NemXGbUTSU2/7vsv2p30rPe/82XrTjslJD5fxOw9u1qtgBuC4PkH4k0tZtH01xuhJwQ25ztVl0Y45en7Qp5HkIzQj2CGw6pxZJbO98Jy//7cZW8uvtcntDOdMdd1I0YaPp0JtVDkDrOu6zTumZrKwdSSRwszpJriRbjktoJU4V3sYjKew00+m8950hXeOi214URI91h1Bwe1uQ0JfajGZjPRks9Kje1AClQlHGNVLBR5tPboSh51D/vyWIfjOsC4a15XFqigS7B4DEL7So3edKqsTHUw74ulZ9GG7Jq1U/Zap2S3sHkmkMHO6iZDJtXZJZCtRrrbhrZmyJte4lIVFpOo6iREU3K4FOvRraaTHs9IzH+kJnv67fuKs5elLIx3x7FvxXhL8la6lMGHyqihWSqmisD6aU1tfkiLnJO+lOdDHgOtkti0LGX07BBG3t7rr1GZoaGv62sjnVwvI43YvtKYHcw6xa3ALU6cTE/pIhSPUYE63EDC51k6LbCXA1URY/YVrEGHNNC7MypkzgsLbddogQt/FrL5I6+ajREkPS0hSWunxrx+2MTVjoi3lpTe/sp3YP6O1Y8X30fiabrcDSI91m5koiaWeDj2kp+3P+vuE+3YIIkhPR/qbdS4ziWtL7wwJpGdH7w0raxKhah59Cpyu8IiTa+u0WCX7XU2G1Ve4xmDSk0ThcePSOcLPSDYIGkZ6vKFvl58365V+F3m1TpT0iFvpMebU1tf1lYIek54suCxClitNNqeZTDPtCU3FSs+k6BsCm/WqKI2l6qT7dgiiSI9+lF4DcC5z6yxvECd7naatqy1MTAbEBbTZtPZ+7ydX3WnhrYS62mxotd4EFAbpcfzg77HbZgQx7ToDpzPHG3qd9Jj1Jkp6WEKS0kqPczlgR9fzOFVZTHrnZyCCSE/dlR2TOwNJb8ymradJ4rBzCAs9Gnp5/2a9Wq2Ix1nS7NshGER6CKYcPHkYFW6opVKrFPk8BHkV1r3d62dHbKeFtRLuaqutHle7H0QqlkThkZ7pYeim6ftmBEU+09MTevKZnpRJj5CVnjbfkTf76ytfg+B2gZqSDgyFz2ojr9cGOiYbpIfwxqzammuo7sRsfeieBvQPgbZbSOjbIQgnPd2I0PpX82HpfD/Ft4RojhRfnnevkpma3cLMRfayhdmrfx3euLhOC2klxtVEWJm2OrWoRYl84uKe6/RYdwSFt2uc5TONXG5ImfSwhGTplR7jwRDqsO5iZ82jLZngtMB0Yu2xZfNxBer2Fu2NGbXVFaDU1fTqDEswIEsi+A4v07eFLZ1FrfSU/l8Zad1T9vzuSFehNr6IvujWw9QcUJhqd4HCvf6p4YsL6bTeVga7mr5oIvT33gbNJC6kD5hW3BHUmzYCQl/9R9yGHJ307CT4ReZlIctqWdqKQ4buzdBkEUBc0oTQuCRKepZlPB/E3lsSIEtbccjQvRmaLAKIS5oQGpdESQ9WehaBLKtlaSsOGbo3Q5NFAHFJE0LjshPpOTcBsNKzIGR1YlnaikOG7s3QZBFAXNKE0LgMJz3HJwNWepaCLKtlaSsOGbo3Q5NFAHFJE0LjMpz0TC1KqcVfIRAIBAKB7JMMJD0TsbBJKw9HCjrMD1lWy9JWHDJ0b4YmiwDikiaExmUn0jM6/5qaUYUjBR3mhyyrZWkrDhm6N0OTRQBxSRNC4wLSQyMFHeaHLKtlaSsOGbo3Q5NFAHFJE0LjAtJDIwUd5ocsq2VpKw4ZujdDk0UAcUkTQuMyJum5eLL5EeiT5yciPeE/Uz2KXwiYuxkE7VwgZyeiQKsT2VnAo62zKYtv46NejGv1iN1AU4zWaoy2ZHWGUdCXxHu2oXAP7bZZRGTrg2petvDu2x0MayVK7b6Ng4hNJ8gtB/OMS98WE3SvZjL4Zr2yavXpSGxGpKk9AukpzxxquM6Vs4fVqQvjkx7N9MHzWCi4jB87newH6SG2WV0YTD/R/b1Zr1ZFMSQEAdtkB50bW0NPSWtLWyoUU5Oe9DrDKPAn8TZ/ujvptlm7y7/tDrzefMVtbOm6NKR1b817vLEl4bSAVqLiYm6M6Q5pihps1iuXIeUUF6J/EnXa3mPiYu7ruFoZuzx6r/uIbac1tUe+vVWeOXTkzJVJb29NTSRAegwQPGJ5i4Jn5aG6GqfuvHfmWKQnpJ75SE8qnWEUhK/0WHy43t/Q7wfbSeb26lbH8niUbZ2pmWlr2cKsE3REzAiRrfTHRSvhUdI67LnIyiku9Oig66RbIOLSsihtA3W/flxWmoL0TLXSY5qUxkqPuxiwWa9W67W+uLfRQ5T0Zt7BVE/j7/ryoWv7ItraFwb6DvDOAmpnghMYOrhFsVL15r3miRt9Gdy9nmkLWifaTms+9vYT8xpma1pldTleyWHuJTuD7hm+3oQRcXury8J1ONiUxFy5khfjfZOr0zpTM9WWO6GPVThCDdYJBsJnhMhWQuLSHvNNsMQk7pqTV1yo0eGpk/SqOwSaYtXfhul71fNfNyilxic95ZlD6vDpcsoHmYmsPza4jK/PL2SX0SfULuDslWAaCJ7nOlMrS7uUY9m+iLbmpK9REH0stbcgTHpk1dLFukmAyqhYO1Engm3H0Fqs408RMrrDMPBTF6OGPiWHuZfsDOX0Y3J6RJCejmjWl/Ud6yNvdziTLTMteRzJtc7UvOMUOF3hEUlP67RYJYPiUo82n4J6XAgiEu6QRAqPEhdidASTHmoIdBmt5j5Wfts6Vfj1Hpn07MJ4AknPDIxnu/NKj0uD5lj62BnxF/dDh8uk2m71MaLlwh5a4+Q1JnURJ5qpsbcztCeWrBt5tERar7atoVdJFsM7g1hEkR79aO1ZbZJsy+n5qp4JqKHDFA5pvf03t8l1azotvJXQuBjcZ1VdN/jioqeHzElPC+fisof0+Ob3zXqlxad647/55tN7s16Nu9KzI+MJIT3zMJ7t+KSnXgZJnPbEWZ0y6WmXH/QxQpMef0x6SA99Yllo6dPbGTytxJKeOmWYjzYbpIdVkq16eGcQi0Gkp/FydfHZHOq6CekW6hGHvuQWdiMgp2dHtq7TwloJj0tASjNq9V5CZRaXBt3oCHmmhxsCZaGKotDyXLG2Hmez2nUrqvPkeKTnwgmlTlzcgfH0k56S+XrayIi7zG3f8lQgedoTZnVrRmdPx+tSIT3bzVp7yr+7anOjpT/c46cj9hHnxG686nzLdJGlQ3tiDOkx1KxHsa8T9inJhieuM2RGejrPlvp3tozRwOUrZ/0gILnxrftrdo8kUpg53UTEjBDSSmxc3CFvK0HcoLHtySkuRP/01mnmLm4ImFdqfc/HmvypqrpuazTS0/1Ij1JKqSm+vaU/rRm4RD8YwaRHo/fVHMvOYRvqy4zpgLNa87zJzLVgpER6jF7fKVPaDzJ3Rxx1OdJDnGg4w2C6WvdwTyScVhWwptIO2kDQZkH39lavksPcS3YG2zNJk3sfolZ6ysL0gHOIz1dWRwxIbn2te2qmjnRVLVqYOEIiakbobSUqLgFKhpGenOKypfqnp05KMSouzjJQWfiW+PQTzDarI2Pe3tpd8IvMy0KW1bK0HQrtrsm8yMO9BjI0WQQQlzQhNC4gPTRS0GF+yLJalrbikKF7MzRZBBCXNCE0LiA9NFLQYX7IslqWtuKQoXszNFkEEJc0ITQuO5GecxMApGdByLJalrbikKF7MzRZBBCXNCE0LsNJz/HJkIIrU9BhfsiyWpa24pChezM0WQQQlzQhNC7DSQ8EAoFAIBCILBlIeiZiYVjpWRCyrJalrThk6N4MTRYBxCVNCI3LTqRndP6FZ3qWhSyrZWkrDhm6N0OTRQBxSRNC4wLSQyMFHeaHLKtlaSsOGbo3Q5NFAHFJE0LjAtJDIwUd5