SQL 创建存储过程以在表中插入新数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20544752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a stored procedure to insert a new data in a table
提问by Ameen Chaabani
I want to create a stored procedure to insert a new row in a table 'dbo.Terms'
我想创建一个存储过程来在表 'dbo.Terms' 中插入一个新行
CREATE PROCEDURE dbo.terms
@Term_en NVARCHAR(50) = NULL ,
@Createdate DATETIME = NULL ,
@Writer NVARCHAR(50) = NULL ,
@Term_Subdomain NVARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.terms
(
Term_en ,
Createdate ,
Writer ,
Term_Subdomain
)
VALUES
(
@Term_en = 'Cat' ,
@Createdate = '2013-12-12' ,
@Writer = 'Fadi' ,
@Term_Subdomain = 'English'
)
END
GO
But is shows me an Error here ( @Term_en = 'Cat') incorrect syntax Any help?
但是这里向我显示了一个错误（@Term_en = 'Cat'）不正确的语法有什么帮助吗？
回答by u07ch
I presume you want to insert the values cat etc into the table; to do that you need to use the values from your procedures variables. I wouldn't call your procedure the same name as your table it will get all kinds of confusing; you can find some good resources for naming standards (or crib from Adventureworks)
我想您想将值 cat 等插入表中；为此，您需要使用过程变量中的值。我不会将您的程序与您的表同名，它会引起各种混乱；您可以找到一些用于命名标准的好资源（或来自Adventureworks 的婴儿床）
CREATE PROCEDURE dbo.terms
@Term_en NVARCHAR(50) = NULL ,
@Createdate DATETIME = NULL ,
@Writer NVARCHAR(50) = NULL ,
@Term_Subdomain NVARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.terms
(
Term_en ,
Createdate ,
Writer ,
Term_Subdomain
)
VALUES
(
@Term_en,
@Createdate,
@Writer,
@Term_Subdomain
)
END
GO
And to test it
并测试它
exec dbo.terms
@Term_en = 'Cat' ,
@Createdate = '2013-12-12' ,
@Writer = 'Fadi' ,
@Term_Subdomain = 'English'
回答by Metaphor
Here is how to set your defaults for parameters in your proc:
以下是在 proc 中设置参数默认值的方法：
CREATE PROCEDURE dbo.terms
@Term_en NVARCHAR(50) = 'Cat',
@Createdate DATETIME = '2013-12-12',
@Writer NVARCHAR(50) = 'Fadi',
@Term_Subdomain NVARCHAR(50) = 'English'
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.terms
(
Term_en ,
Createdate ,
Writer ,
Term_Subdomain
)
VALUES
(
@Term_en,
@Createdate,
@Writer,
@Term_Subdomain
)
END
GO
回答by Bha15
Your code is not correct. You put value in insert part. You should enter value in execution part
您的代码不正确。您将值放入插入部分。您应该在执行部分输入值
CREATE PROCEDURE dbo.terms
@Term_en NVARCHAR(50) = NULL ,
@Createdate DATETIME = NULL ,
@Writer NVARCHAR(50) = NULL ,
@Term_Subdomain NVARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.terms
(
Term_en,
Createdate,
Writer,
Term_Subdomain
)
VALUES
(
@Term_en ,
@Createdate ,
@Writer ,
@Term_Subdomain
)
END
execute by this
以此执行
exec dbo.terms
@Term_en = 'Cat' ,
@Createdate = '2013-12-12' ,
@Writer = 'Fadi' ,
@Term_Subdomain = 'English'
GO
回答by Bha15
-- =============================================
-- Author: xxxx
-- Create date: xx-xx-xxxx
-- Description: Procedure for Inserting Data in table
-- =============================================
CREATE PROCEDURE [dbo].[SP_Emp_Insert]
(
@Empname nvarchar(250)=null,
@Status int=null,
@LoginUserId nvarchar(50)=null,
@Msg nvarchar(MAX)=null OUTPUT
)
AS
BEGIN TRY
INSERT INTO tbl_Employee
VALUES
(
@Empname ,
@Status,
GETDATE(),
GETDATE(),
@LoginUserId
)
SET @Msg='Table Detail Saved Successfully.'
END TRY
BEGIN CATCH
SET @Msg=ERROR_MESSAGE()
END CATCH
GO
