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

Today, I will talk to you about how to create stored procedures in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Create a stored procedure whose table name and comparison field can be used as parameters

Create PROCEDURE sp_getTeacherInfo @ TblName nvarchar (30),-- table name @ CmpStr nvarchar (30),-- value participating in the comparison @ TblAtr nvarchar (30)-- Field AS DECLARE @ sql varchar (4000) SET @ sql = 'select * from' + @ TblName + 'where' + @ TblAtr +'='+ @ CmpStr EXEC (@ sql) Table tbl_TeacherInfo Exec sp_getTeacherInfo 'tbl_TeacherInfo','TeaNo' '07417502'/ / Note: calls like this are incorrect and are restored to the query statement select * from tbl_TeacherInfo where TeaNo = 07417502 because there is no error. Because the parameter '07417502' is mistaken for an integer An integer comparison is made between Exec sp_getTeacherInfo 'tbl_TeacherInfo','Name',' Chuliuxiang' / / the error is restored to the query statement select * from tbl_TeacherInfo where TeaNo = Chuliuxiang / / the correct calling method Exec sp_getTeacherInfo 'tbl_TeacherInfo','Name' is obviously wrong. "Chuliuxiang" is restored to the query statement select * from tbl_TeacherInfo where TeaNo = "Chuliuxiang" regular stored procedure creation Create PROCEDURE sp_AddRowToLogin @ TeaNo nvarchar,-- comparison field @ TeaName nvarchar (100)-- comparison field AS insert into tbl_UserLogin values (@ TeaNo,@TeaName,@TeaNo 0) stored procedure CREATE PROCEDURE sp_Alter @ TblName nvarchar (30) returned except for the specified column-table name AS declare @ sql varchar (1000) select @ sql='select 'select @ sql=@sql+name+',' from syscolumns where id=object_id (@ TblName) and name not in (' ID','TeaNo') select @ sql=left (@ sql,len (@ sql)-1) select @ sql=@sql+' from'+ @ TblName exec (@ sql) / / not returned except ID and TeaNo Everything else goes back.

After reading the above, do you have any further understanding of how to create stored procedures in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.