ALTER procedure [dbo].[proc_GetAllBooks]
@order varchar(20)
as
declare @sqlstr varchar(max)
set @sqlstr='select Books.*,Categories.Name as CategoryName,Publishers.Name as PublisherName from Books inner join Categories on Books.CategoryId=Categories.Id inner join Publishers on Books.PublisherId=Publishers.Id order by '+@order+' desc'
go我调用存储过程 但是显示不了数据 请各位大侠帮我看看
@order varchar(20)
as
declare @sqlstr varchar(max)
set @sqlstr='select Books.*,Categories.Name as CategoryName,Publishers.Name as PublisherName from Books inner join Categories on Books.CategoryId=Categories.Id inner join Publishers on Books.PublisherId=Publishers.Id order by '+@order+' desc'
go我调用存储过程 但是显示不了数据 请各位大侠帮我看看
declare @sqlstr varchar(4000)
我是这样调的
@order varchar(20)
as
declare @sqlstr varchar(max)
set @sqlstr='select Books.*,Categories.Name as CategoryName,Publishers.Name as PublisherName from Books inner join Categories on Books.CategoryId=Categories.Id inner join Publishers on Books.PublisherId=Publishers.Id order by '+@order+' desc'
exec(@sqlstr)
goexec [proc_GetAllBooks] @order=PublishDate 加了一个exec(@sqlstr)语句就有数据了
修改
create procedure [dbo].[proc_GetAllBooks] 调用使用exec ()你是在数据库执行吗?
@order varchar(20)
as
declare @sqlstr varchar(max)
set @sqlstr='select Books.*,Categories.Name as CategoryName,Publishers.Name as PublisherName from Books inner join Categories on Books.CategoryId=Categories.Id inner join Publishers on Books.PublisherId=Publishers.Id'
if(@order<>null)
begin
set @sqlstr=@sqlstr+' order by '+@order
end
exec(@sqlstr)
这样好像不行
select *
from Books inner join Categories on Books.CategoryId=Categories.Id
inner join Publishers on Books.PublisherId=Publishers.Id
begin
set @sqlstr=@sqlstr+' order by '+@order
end
exec(@sqlstr)
你打印一下SQL看下句子正确吗?
@order varchar(20) --@order不能为空
as
declare @sqlstr varchar(max)
set @sqlstr='
select
Books.*,
Categories.Name as CategoryName,
Publishers.Name as PublisherName
from Books inner join Categories
on Books.CategoryId=Categories.Id
inner join Publishers
on Books.PublisherId=Publishers.Id
order by '+@order+' desc
'
exec(@sqlstr)
go
create procedure [dbo].[proc_GetAllBooks]
@order varchar(20)=null
as
declare @sqlstr varchar(max)
set @sqlstr='
select
Books.*,
Categories.Name as CategoryName,
Publishers.Name as PublisherName
from Books inner join Categories
on Books.CategoryId=Categories.Id
inner join Publishers
on Books.PublisherId=Publishers.Id'
+isnull(' order by '+@order,'')--可以利用isnull简化语句
exec(@sqlstr)
NULL不是用<>来比较,是用IS NULL OR IS NOT NULL
as
declare @sqlstr varchar(max)
set @sqlstr = 'select Books.*,Categories.Name as CategoryName,Publishers.Name as PublisherName from Books inner join Categories on Books.CategoryId=Categories.Id inner join Publishers on Books.PublisherId=Publishers.Id order by '+@order+' desc'
exec( @sqlstr )
go