Creating a Stored Procedure
Table name and comparison field a stored procedure that can be used as a parameter
Create PROCEDURE Sp_getteacherinfo
@TblName nvarchar (30),--table name
@CmpStr nvarchar (30),--value to participate in the comparison
@TblAtr nvarchar (30)--fields that participate in the comparison
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 wrong
Revert to query statement
SELECT * from tbl_teacherinfo where Teano = 07417502
The reason why there is no error is that the parameter "07417502" is mistaken for an integral type, and a comparison of integers
Exec sp_getteacherinfo "Tbl_teacherinfo", "Name", "Chu Liuxiang"
Error
Revert to query statement
SELECT * from tbl_teacherinfo where Teano = Chu Liuxiang
It's obviously wrong.
Correct method of calling
Exec sp_getteacherinfo "Tbl_teacherinfo", "Name", "Chu Liuxiang" "
Revert to query statement
SELECT * from tbl_teacherinfo where Teano = "Chu Liuxiang"
Creation of general stored procedures
Create PROCEDURE Sp_addrowtologin
@TeaNo nvarchar (100),--comparing fields
@TeaName nvarchar (100)--comparing fields
As
INSERT into Tbl_userlogin values (@TeaNo, @TeaName, @TeaNo, 0)
Stored procedures that are returned except for the specified column
CREATE PROCEDURE Sp_alter
@TblName nvarchar (30)--table name
As
DECLARE @sql varchar (1000)
Select @sql = "Select"
Select @sql = @sql +name+ "," from Syscolumns where id=object_id (@TblName) and name isn't in ("id", "Teano")
Select @sql =left (@sql, Len (@sql)-1)
Select @sql = @sql + "from" + @TblName
EXEC (@sql)
Except that the ID and Teano two columns do not return, the other returns