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

problem regarding relationship

this is my diagram http://aspspider.net/vhalexxs/relationship.jpg

here is the error while saving the diagram...

'rco_prodattr' table saved successfully
'rco_prodacc' table
- Unable to create relationship 'FK_rco_prodacc_rco_prodattr1'.
Introducing FOREIGN KEY constraint 'FK_rco_prodacc_rco_prodattr1' on table 'rco_prodacc' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

is there a way to create a constrain with cascade delete and update on rco_prodacc, or if not what will be the best possible solution for this.

thanks....

Do not enforce second relationship and maintain it by triggers on rco_prodattr.|||

does trigger also roll back when you perform a rollback transaction command in SQL

|||Rollback command rollbacks the transaction whatever it called from within.sql

problem regarding foreign key

i have 3 table which is merchant, merchantcategory and merchantitem.

merchantcategory's foreign key is refer to merchant_id, and merchantitem's foreign key is refer to merchantcategory_id.....

the problem is like this...

i would like to select all the available items from merchantitem, but i only have the information of merchant_id, so what method should i use to get these output ??

i am using the functions which generated by codesmith with wilson ORMapper method....

i really hope that someone will provide me the solution.....thanks

If I understand correctly, I think:

SELECT merchant.Name, merchantitem.someitem1, merchantitem.someitem2, merchantitem.someitem3
FROM (merchant INNER JOIN merchantcategory ON merchant.Merchant_ID = merchantcategory.merchant_id) INNER JOIN merchantitem ON merchantcategory.merchantcategory_id = merchantitem.merchantcategory_id;

should be pretty much what you need.

hth

|||

thanks fUNKYgIBBON

i know this method......but now i am using the functions which is generated by codesmith...

so i cannot use this method......

Problem installing SQL 2k 32bit on Windows 2003 64bit x86 SP1

I've been having no luck trying to install SQL 2k 32bit on Windows 2003 64bit x86 SP1 - I've found several articles regarding needing to put SP4 on the 64bit OS, but my problem is the SQL 2k 32bit ",,\x86\setup\setupsql" won't run.

I consistantly receive a "...(localuserdir)\Local.ins. Unable to load installaion instructions. Error 703."

And of course, the SP4 setup won't run because there is not an existing SQL Service to upgrade.

Anyone come up against a similar problem?

Thanks!

Are we talking about an X64 machine here? You cant install 32-bit on IA64.