这个是我刚才写的stored procedure, 代码如下
这个是我模仿别人写的一个stored procedure,但是编译没有通过,自己检查了几遍还是没看出来哪里有错,请大家帮我看看吧。CREATE PROCEDURE sp_insertCustomer@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)ASbegin
DECLARE @sql varchar(4000)
SET @sql = ''
DECLARE @idchar varchar(50),
@birthchar varchar(50) SET @idchar = @id
SET @birthchar = @birth
@sql = CASE
When @id <> 0 AND @idchar IS NOT NULL then @idchar
Else NULL
END
+ CASE
When @name IS NOT NULL then + ', ''' + @name + ''''
Else NULL
END
+ CASE
When datediff( day, @birth, getdate() ) <> 0 then ', ''' + @birthchar + ''''
Else NULL
END
+ CASE
When @address IS NOT NULL then + ', ''' + @address + ''''
Else NULL
END
+ CASE
When @phone IS NOT NULL then + ', ''' + @phone + ''''
Else NULL
END
+ CASE
When @state IS NOT NULL then + ', ''' + @state + ''''
Else NULL
END if ( @sql <> '')
begin
@sql = stuff(@sql, 1, 0, 'INSERT INTO Customer (ID,NAME,BIRTH,ADDRESS,PHONE,STATE)
VALUES ( ')
@sql = @sql + ' )'
exec (@sql)
end
end
下面是我的到的错误信息:Msg 102, Level 15, State 1, Procedure sp_insertCustomer, Line 22
'@sql' 附近有语法错误。
Msg 102, Level 15, State 1, Procedure sp_insertCustomer, Line 49
'@sql' 附近有语法错误。
Msg 156, Level 15, State 1, Procedure sp_insertCustomer, Line 56
关键字 'end' 附近有语法错误。
这个是我模仿别人写的一个stored procedure,但是编译没有通过,自己检查了几遍还是没看出来哪里有错,请大家帮我看看吧。CREATE PROCEDURE sp_insertCustomer@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)ASbegin
DECLARE @sql varchar(4000)
SET @sql = ''
DECLARE @idchar varchar(50),
@birthchar varchar(50) SET @idchar = @id
SET @birthchar = @birth
@sql = CASE
When @id <> 0 AND @idchar IS NOT NULL then @idchar
Else NULL
END
+ CASE
When @name IS NOT NULL then + ', ''' + @name + ''''
Else NULL
END
+ CASE
When datediff( day, @birth, getdate() ) <> 0 then ', ''' + @birthchar + ''''
Else NULL
END
+ CASE
When @address IS NOT NULL then + ', ''' + @address + ''''
Else NULL
END
+ CASE
When @phone IS NOT NULL then + ', ''' + @phone + ''''
Else NULL
END
+ CASE
When @state IS NOT NULL then + ', ''' + @state + ''''
Else NULL
END if ( @sql <> '')
begin
@sql = stuff(@sql, 1, 0, 'INSERT INTO Customer (ID,NAME,BIRTH,ADDRESS,PHONE,STATE)
VALUES ( ')
@sql = @sql + ' )'
exec (@sql)
end
end
下面是我的到的错误信息:Msg 102, Level 15, State 1, Procedure sp_insertCustomer, Line 22
'@sql' 附近有语法错误。
Msg 102, Level 15, State 1, Procedure sp_insertCustomer, Line 49
'@sql' 附近有语法错误。
Msg 156, Level 15, State 1, Procedure sp_insertCustomer, Line 56
关键字 'end' 附近有语法错误。
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5) AS begin
DECLARE @sql varchar(4000)
SET @sql = '' DECLARE @idchar varchar(50),
@birthchar varchar(50) SET @idchar = @id
SET @birthchar = @birth SET @sql = CASE
When @id <> 0 AND @idchar IS NOT NULL then @idchar
Else NULL
END
+ CASE
When @name IS NOT NULL then + ', ''' + @name + ''''
Else NULL
END
+ CASE
When datediff( day, @birth, getdate() ) <> 0 then ', ''' + @birthchar + ''''
Else NULL
END
+ CASE
When @address IS NOT NULL then + ', ''' + @address + ''''
Else NULL
END
+ CASE
When @phone IS NOT NULL then + ', ''' + @phone + ''''
Else NULL
END
+ CASE
When @state IS NOT NULL then + ', ''' + @state + ''''
Else NULL
END if ( @sql <> '')
begin
@sql = stuff(@sql, 1, 0, 'INSERT INTO Customer (ID,NAME,BIRTH,ADDRESS,PHONE,STATE)
VALUES ( ')
@sql = @sql + ' )' exec (@sql)
end end