sqlserver存储过程

- 日理万妓 2022-05-19 13:53 217阅读 0赞

插入

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