练习动态语句时,我自己出了一个题目,报错
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@letter1'。
[Northwind] 中选择出lastname以特定字母开始的存储过程ALTER PROCEDURE 选择一个字母开头的员工
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE '+'@letter1%'+''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString--, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
服务器: 消息 137,级别 15,状态 2,行 1
必须声明变量 '@letter1'。
[Northwind] 中选择出lastname以特定字母开始的存储过程ALTER PROCEDURE 选择一个字母开头的员工
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE '+'@letter1%'+''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString--, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE '+'@letter1%'+''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString--, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 4000)
SET @SqlString =N'select * from employees WHERE lastname LIKE '''+@letter+'%''' exec @SqlString
END
SET @letter1='1'
PRINT(N'select * from NUM WHERE lastname LIKE '+@letter1+'%''' )
/*select * from NUM WHERE lastname LIKE 1%'
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE '+''@letter1%''+''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString--, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
ALTER PROCEDURE 选择一个字母开头的员工
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE ''' + @letter + '%'''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString--, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
这样就OK了。
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 4000)
DECLARE @ParaDefinition VARCHAR( 1000)
declare @letter1 nvarchar(10)
SET @SqlString =N'select * from employees WHERE lastname LIKE '''+@letter+'%'''
SET @ParaDefinition =N'@letter1 nvarchar(10)'
EXECUTE sp_executesql @SQLString, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
后面是 LIKE'+'@letter1%'+''
后面是 LIKE'+'@letter1%'+''
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE ''' + @letter + '%'''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString--, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'
不行吗???
@letter NVARCHAR(10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 4000)
DECLARE @ParaDefinition VARCHAR( 1000)
--declare @letter1 nvarchar(10)
SET @SqlString =N'select * from employees WHERE lastname LIKE ''@letter1%'''
SET @ParaDefinition =N'@letter1 nvarchar(10)'
EXECUTE sp_executesql @SQLString, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'???
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
SET @SqlString =N'select * from employees WHERE lastname LIKE ''' + @letter + '%'''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString, @ParaDefinition, @letter1 = @letter
END EXECUTE [选择一个字母开头的员工] 'a'一定要用letter1作为参数。,不要用letter 这里也许有些无理取闹,但是能实现么???
letter1???
上面的注释符号是错误的
您的好像正确答案哦,但是还是报错啊
服务器: 消息 214,级别 16,状态 3,过程 sp_executesql,行 10
过程需要参数 '@parameters' 为 'ntext/nchar/nvarchar' 类型。
@letter VARCHAR(10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 4000) declare @letter1 varchar(10)
SET @SqlString =N'select * from employees WHERE lastname LIKE '''+@letter1+'%''' EXECUTE sp_executesql @SQLString, N'@letter1 varchar(10)', @letter1 = @letter
END
EXECUTE k 'a'
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 1000)
DECLARE @ParaDefinition VARCHAR( 1000)
DECLARE @letter1 NVARCHAR( 10)
set @letter1=@letter
SET @SqlString =N'select * from employees WHERE lastname LIKE ''' + @letter + '%'''
-- SET @ParaDefinition =N'@letter1 nvarchar(100)'
EXECUTE sp_executesql @SQLString, @ParaDefinition, @letter1 = @letter
END 那只能这样吧? @letter和 @letter1是两个完全不同的参数
@letter NVARCHAR( 10)
AS
BEGIN
DECLARE @SqlString NVARCHAR( 4000)
SET @SqlString =N'select * from employees WHERE lastname LIKE '''+@letter+'%''' exec (@SqlString)
END EXECUTE 选择一个字母开头的员工 'f'