Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
i have create a procedure and after executing it will show error msg.
 
Please help me
Must declare the scalar variable "@ArticleN".
 
/****** Object:  StoredProcedure [dbo].[vs_Models_SELECT]    Script Date: 11/20/2012 10:45:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Models]
 
AS
 

	SET NOCOUNT OFF;
 
Declare @CategoryID int
Declare @SubCategoryID int
Declare @SortExpression varchar(100)
Declare @Order varchar(100)
Declare  @ArticleN varchar(150)
 
set @CategoryID=6  
set @SubCategoryID=1   
set @SortExpression=NULL
set @Order=NULL
 

if @SortExpression is NULL 
Begin
set @SortExpression='p.SeriesId  '
End
 
if @Order is NULL 
Begin
set @Order ='Desc  '
End
 
declare @Sort varchar(200)
set @Sort=@SortExpression + @order
set @ArticleN=N'A P I INDIA';
 
declare @QueryString nvarchar(max)
 
		set @QueryString= ' Select  --ProductID ,
				Article_No as Model,
				p.SeriesId,
				s.SeriesDesc,
				p.CategoryId,
				ca.CategoryDesc,
				p.MaterialUsedId,
				m.MaterialUsedDesc,
				p.Size, 
				p.ColorId,
				c.ColorNameDesc,
				p.Gender,
				MRP ,
				p.Manufactured_In,
				Maunfacturing_Date,
				Input_Pairs,
				Soldout_Pairs,
     			Remaining_Pairs
 
		from tabproduct P
		inner join tblkpSeries s
		on p.SeriesId=s.SeriesId
		inner join tblkpCategory ca
		on p.CategoryId=ca.CategoryId
		inner join tblkpMaterialUsed m
		on p.MaterialUsedId=m.MaterialUsedId
 
		inner join tblkpColor c
		on p.ColorId=c.ColorId
 
		where 
   		  
		Manufactured_In=case when  @ArticleN  is null then Manufactured_In
			else @ArticleN
			end
	
 
		Order by ' + @Sort
 
PRINT @QueryString
 EXEC (@QueryString)
 
Thanks in advance..
Posted 19-Nov-12 22:54pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I think you meant to include the content of @ArticleN in your string, rather than the name:
Change:
where
 
Manufactured_In=case when  @ArticleN  is null then Manufactured_In
    else @ArticleN
    end
 

Order by ' + @Sort
 
To:
where
 
Manufactured_In=case when ' + @ArticleN + ' is null then Manufactured_In
    else ' + @ArticleN + '
    end
 

Order by ' + @Sort
  Permalink  
Comments
DINESH K MAURYA at 20-Nov-12 4:14am
   
This return :-
where
Manufactured_In=case when A P I INDIA is null then Manufactured_In
else A P I INDIA
end
Order by p.SeriesId Desc
 
An expression of non-boolean type specified in a context where a condition is expected, near 'P'.
OriginalGriff at 20-Nov-12 4:30am
   
Then enclose it in escaped quotes:
when ''' + @ArticleN + ''' is null...
DINESH K MAURYA at 20-Nov-12 4:41am
   
Thanks
sariqkhan at 20-Nov-12 4:44am
   
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 420
1 OriginalGriff 210
2 Kornfeld Eliyahu Peter 210
3 DamithSL 210
4 mukesh mourya 85
0 OriginalGriff 7,215
1 DamithSL 5,199
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,757
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 20 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100