PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE

Hi

I am WORKING IN AN APPLICATION USING SQL SERVER 2000 AND VB6

I'VE A PROBLEM REGARDING VARIABLE PASSING TO STORED PROCEDURE

I WILL EXPLAIN WITH AN EXAMPLE

TABLE STRUCTURE

AccAccounts

Accid(Numeric) AccName(Varchar)

-

1 Cash A/c

2 Students A/c

3 HDFC Bank A/c

my Application will pass the "Accid" as a string format to Stored Procedure

STORED PROCEDURE

--

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Select * from Accaccount where accid in (@.Accid)

when i run this SP

declare @.Accid Varchar(100)
set @.Accid ='1,2'

exec GetAccName @.Accid

i get the following error

Server: Msg 8114, Level 16, State 5, Procedure GetAccName, Line 4
Error converting data type varchar to numeric.

please "ANY ONE" help me!!.

The above example is only an example

REGARDS

JAMES

Hi,

You can not use the @.Variable which holds the multiple accountids in Static SQL statement.. you need to use dynamic sql..

Code Snippet

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Declare @.cmd varchar(4000)

Set @.Cmd = 'Select * from Accaccount where accid in (' + @.Accid')'

exec (@.cmd)

But would suggest you to go through the following link to see the advantages and disadvantages..

http://www.sommarskog.se/dynamic_sql.html

Regards,

|||

You can also do this without dynamic sql.

Code Snippet

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Select *
from AccAccounts
where charindex(','+convert(varchar,accid)+',',','+@.Accid+',')>0

|||

THANK U VERY MUCH

|||

Hi James

You shouldn't pass comma separated values to the stored procedure, like: '1,2,3' . Because when you call the sproc it is trying to convert your value(because accid is numeric in your table) to varchar implicitly and due to the ',' in your data the conversion is going to fail and throws an error. You need to write some other logic to get it done.

Thanks & Regards,

Kiran.Y

|||

HI

THX FOR REPLY

WHAT IN CASE IF I NEED A QRY

Select * from AccAccounts where Accid Not in (1,2)

|||

>0 means a match is found

=0 means no match.

Code Snippet

CREATE PROCEDURE GetAccName
@.Accid Varchar(100)
AS
Select *
from AccAccounts
where charindex(','+convert(varchar,accid)+',',','+@.Accid+',') = 0

|||

Thx