Click here to Skip to main content
15,884,176 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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

1 solution

I think you meant to include the content of @ArticleN in your string, rather than the name:
Change:
SQL
where

Manufactured_In=case when  @ArticleN  is null then Manufactured_In
    else @ArticleN
    end


Order by ' + @Sort


To:
SQL
where

Manufactured_In=case when ' + @ArticleN + ' is null then Manufactured_In
    else ' + @ArticleN + '
    end


Order by ' + @Sort
 
Share this answer
 
Comments
DINESH K MAURYA 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 20-Nov-12 4:30am    
Then enclose it in escaped quotes:
when ''' + @ArticleN + ''' is null...
DINESH K MAURYA 20-Nov-12 4:41am    
Thanks
sariqkhan 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900