I am getting scalar variable not define @cmdSQL
while trying to print my query to see what is d sql query is getting generating while running
print @cmdSQL = @cmdSQL + ' from server_des where title Like ''%'+@title+'%'' AND Description Like ''%'+@Description+'%'' AND Keywords Like ''%'+@Keywords+'%'' AND category Like ''%'+@Category+'%'' AND Location Like ''%'+@Location+'%''AND source Like ''%'+@Source+'%'' AND Date Like ''%'+@Date+'%'
exec All_Searchnew1 'man','thief','ma','ma','ma','ma','12/12/2009',1,1,1,0,0,0,0,0
procedure Is written below...
Alter PROCEDURE [dbo].[All_Searchnew1]
@title nvarchar(max),
@Description nvarchar(max),
@Keywords nvarchar(max),
@Category nvarchar(max),
@Location nvarchar(max),
@Source nvarchar(max),
@Date nvarchar(50),
@RD_btn_AND_OR AS BIT,
@Chk_title AS BIT ,
@Chk_Description AS Bit ,
@Chk_Keywords AS BIT,
@Chk_Category AS BIT,
@Chk_Location AS BIT,
@Chk_Source AS BIT,
@Chk_Date AS BIT
AS
DECLARE @cmdSQL AS VARCHAR(2000)
SET @cmdSQL = 'Select '
DECLARE @cmdCondition AS VARCHAR(2000)
SET @cmdCondition = ''
if @RD_btn_AND_OR = 1
Begin
if (@Chk_title = 1)
begin
SET @cmdSQL = @cmdSQL + ' title,'
SET @cmdCondition ='title Like ''%' + @title + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as title'
SET @cmdCondition ='title Like ''%' + @title + '%'''
end
if @Chk_Description <> 1
begin
SET @cmdSQL = @cmdSQL + ' Description,'
SET @cmdCondition ='Description Like ''%' + @Description + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as Description'
SET @cmdCondition ='Description Like ''%' + @Description + '%'''
end
if @Chk_Keywords <> 1
begin
SET @cmdSQL = @cmdSQL + ' Keywords,'
SET @cmdCondition ='Keywords Like ''%' + @Keywords + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as Keywords'
SET @cmdCondition ='Keywords Like ''%' + @Keywords + '%'''
end
if @Chk_Category <> 1
begin
SET @cmdSQL = @cmdSQL + ' Category,'
SET @cmdCondition ='Category Like ''%' + @Category + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as Category'
SET @cmdCondition ='Category Like ''%' + @Category + '%'''
end
if @Chk_Location <> 1
begin
SET @cmdSQL = @cmdSQL + ' Location,'
SET @cmdCondition ='Location Like ''%' + @Location + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as Location'
SET @cmdCondition ='Location Like ''%' + @Location + '%'''
end
if @Chk_Source <> 1
begin
SET @cmdSQL = @cmdSQL + ' Source,'
SET @cmdCondition ='Source Like ''%' + @Source + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as Source'
SET @cmdCondition ='Source Like ''%' + @Source + '%'''
end
if @Chk_Date <> 1
begin
SET @cmdSQL = @cmdSQL + ' Date'
SET @cmdCondition ='Date Like ''%' + @Date + '%'''
end
else
begin
SET @cmdSQL = @cmdSQL +'null as Date'
SET @cmdCondition ='Date Like ''%' + @Date + '%'''
end
End
--SET @cmdSQL = @cmdSQL + ' from server_des where title like ''%''' + @title + '''%'' AND Description Like '''%''+@Description+''%''' AND Keywords Like '''%''+@Keywords+''%''' AND category Like '''%''+@Category+''%''' AND Location Like '''%''+@Location+''%'''AND source Like '''%''+@Source+''%''' AND Date Like '''%''+@Date+''%''
--SET @cmdSQL = @cmdSQL + ' from server_des where title Like ''%' + @title + '%''or description like ''%'+@Description+'%''or keywords like ''%'+@Keywords+'%''or category like ''%'+@Category+'%'''
SET @cmdCondition = @cmdCondition
SET @cmdSQL = @cmdSQL + ' from server_des where '开发者_如何学C + @cmdCondition
EXEC(@cmdSQL)
go
On the face of it, you are running
print @cmdSQL = @cmdSQL + ...
before it's declared which is only in the stored procedure
Also you missed commas in expressions like this:
SET @cmdSQL = @cmdSQL +'null as title'
It should be SET @cmdSQL = @cmdSQL +'null as title,'
Missing something is common problem when combining dynamic SQL from many pieces. This work should be done very attentively.
精彩评论