使用@variable将MSsQL转换为MySql存储过程

I used this SP with MsSQL. How do I convert it to MySql?

我在MsSQL中使用了这个SP。如何将其转换为MySql?

It would not allow me to create procedure because of syntax errors.

由于语法错误,它不允许我创建过程。

The goal is to receive a string from a user like this: (ReportId,UserName) - if found in the variable, send back Allowed else send back Access denied.

目标是从这样的用户接收一个字符串:(ReportId,UserName) - 如果在变量中找到,则发送回允许其他发送回拒绝访问。

Thanks.

Procedure [dbo].[Permission]
@ReportId   As Int
,@UserName  As Varchar(50)
As
Declare
    @Result As Varchar(250)

Select
    @Result = IsNull((
        Select
            IsNull([permission].[Message], '')

        From
            [permission]
            Join
            [users]
                On [users].[ID] = [permission].[User ID]

        Where
            [users].[User Name] = @UserName
            And [permission].[Report ID] = @ReportId
    ), 'Access denied')


Select
    Case
        When @Result = '' Then 'Allowed'
        Else @Result
    End [Result]

2 个解决方案

#1

Not comprehensive list but:

不完整的清单,但:

Remove @ symbols, those are for session variables; alternatively, you may want to replace them with some sort of standardized prefixes to prevent potential ambiguity with field names from tables.

删除@符号,这些符号用于会话变量;或者,您可能希望用某种标准化前缀替换它们,以防止表中字段名称的潜在歧义。

Replace [ and ] with `

将[和]替换为`

IsNull becomes IfNull

IsNull成为IfNull

when doing assignments, like SELECT @Result = IsNull(..., change = to :=

在做作业时,比如SELECT @Result = IsNull(...,change = to:=

Also, end your statements with ;, I have no idea why so many MSSQL users love skipping them.

另外,结束你的陈述;我不知道为什么这么多MSSQL用户喜欢跳过它们。

For general create proc syntax, go to CREATE PROCEDURE.

有关常规create proc语法,请转到CREATE PROCEDURE。

#2

I translated your SP to MySQL Syntax:

我将您的SP翻译为MySQL语法:

DELIMITER $$
DROP PROCEDURE if exists Permission$$
CREATE PROCEDURE Permission(
    p_ReportId   Int,
    p_UserName  Varchar(50)
    )
BEGIN
    set @Result = '';

    set @Result = 
    coalesce(
       (Select
            coalesce(permission.Message, '')
        From permission Inner Join users
            On users.ID = permission.`User ID`
        Where
            users.`User Name` = p_UserName
            And permission.`Report ID` = p_ReportId)
        , 'Access denied');


    Select
        Case
            When @Result = '' Then 'Allowed'
            Else @Result
        End as Result;
END $$

delimiter ;

#1