sqlserver存储过程

插入

CREATE PROCEDURE testInsert
	@id bigint OUTPUT,
    @uid int,
    @name nvarchaer(50)
AS
BEGIN
	DECLARE @MyTableVar table( id bigint);
	BEGIN TRY
		       INSERT INTO Table_1(uid,name)
			   output inserted.id into @MyTableVar
			    VALUES(@uid,@name)
		SET @id = (SELECT TOP 1 id FROM @MyTableVar)
	END TRY  
	BEGIN CATCH 
		RETURN 0
	END CATCH
END
RETURN 1

更新

//nvarchar 的字段要加上引号，但是引号具体为什么这么加，我还没弄明白
CREATE PROCEDURE testUpdate
@id int,
@uid int,
@name nvarchar(50)
AS
 exec('UPDATE Table_1 set uid='+@uid+',name='''+@name+''' where id='+@id)


//==============================================================================

CREATE PROCEDURE testUpdate
@id int,
@uid int,
@name nvarchar(50)
AS
BEGIN
	BEGIN TRY
		UPDATE Table_1 
		SET     uid=@uid,name=@name
		WHERE ([id] = @id)

	END TRY  
	BEGIN CATCH 
		RETURN 0
	END CATCH
END
RETURN 1

查询

//表名作为参数
CREATE PROCEDURE testSelect
@tableName nvarchar(20)
AS
 EXEC('select * from '+@tableName)

//====================================================================

CREATE PROCEDURE testSelect
	@id bigint ,
	@uid int OUTPUT,
	@name nvarchar(20) OUTPUT,
AS
BEGIN
DECLARE @RET int
	BEGIN TRY
		IF EXISTS(SELECT [id] FROM [Table_1] WHERE [id]=@id) BEGIN
			SELECT TOP 1 @uid =[uid ],@name =[name]
			FROM [Table_1]
			WHERE [id]=@id
			SET @RET = 1 
		END ELSE BEGIN
			SET @RET = 0
		END
	END TRY  
	BEGIN CATCH 
		RETURN -1
	END CATCH
END
RETURN @RET

删除

//（表明作为参数）
CREATE PROCEDURE testDelete
@tableName nvarchar(20),
@id int
AS
EXEC('DELETE from '+@tableName+' where id'=@id)

//===============================================================================

CREATE PROCEDURE testDelete
	@id bigint
AS
BEGIN
	BEGIN TRY
		DELETE FROM Table_1
		WHERE (id = @id)
	END TRY  
	BEGIN CATCH 
		RETURN 0
	END CATCH
END
RETURN 1