Using varchar type local variable in the store procedure "IN (@localVarCharVariable)" clause.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using varchar type local variable in the store procedure "IN (@localVarCharVariable)" clause.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-24 : 08:42:22

Sagar Raut writes "Dear Friends,
I am facing problem for fetching the record for the following store procedure:

Create PROCEDURE P_GetTRUpdateListing
@StrStatus Varchar(20) ,
AS
Begin
Select @StrStatus = Replace(@StrStatus,'|','''')
Select * From TR_DOMESTIC_TRAVEL_REQUISITION DTR
Where DTR.TDTR_PDTR_STATUS in (@StrStatus)
End
Go

Calling the above procedure:
exec P_GetTRUpdateListing '|P|,|U|'

In the above the if @StrStatus is Int type then it is executing properly.
For single value like exec P_GetTRUpdateListing '|P|' it is working propely."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 09:04:39

you can't use in like that... there are several articles around about it...

you can use this function in a join


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	While (Charindex ( @SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))

		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
	End

	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

or you could do:

Where ','+@StrStatus+',' like '%,'''+DTR.TDTR_PDTR_STATUS+''',%'

Corey

Kristen
Test

22859 Posts

Posted - 2004-09-24 : 09:05:31

You cannot do IN (@xxx) I'm afraid.

Best bet is to use a "splitter function" to present the data in @strStatus as a temporary table and JOIN that to your main table, rather than using IN.

Should be plenty of examples on here if you search for SPLITTER or CSV

Kristen

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-24 : 09:06:05

Seems like this question asked every day.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-24 : 09:25:32

quote:

Seems like this question asked every day.

yup ...

http://www.sqlteam.com/item.asp?ItemID=11499

it's a fundamental concept of programming to understand the difference between a variable value and actual code that many people never grasp with SQL.

What if i had:

set @Var= 'Smith, Jeff'
select * from sometable where Name IN (@Var)

Should that parse "Smith" and "Jeff" into two separate values and compare all names for either Jeff or Smith but not "Smith, Jeff" ??

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources