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 referencing a column in a Table Variable in a query.

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @.THeader" . @.THeader is the name of the variable table and the error occurs with such references as @.THeader.ApplyAmt, @.THeader.TransactionHeaderID, etc.

declare @.THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @.THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @.THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

Update @.THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @.THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @.THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @.THeader.ApplyAmt
FROM @.THeader, mtblTransactionHeader
WHERE @.THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table

Thanks in advance

smHaig

Try adding an alias to @.THeader

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = t.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = t.ApplyAmt
FROM @.THeader t, mtblTransactionHeader
WHERE t.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID -- put the values back into original table

Problem passing a variable into a table-valued function

Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?select car.id, car.licenceNumber, car.brand, car.model,
dbo.getCurrentDriver(car.id) as driverName
from car|||[sniped]

select car.id, car.licenceNumber, car.brand, car.model,
, dbo.getCurrentDriver(car.id) as driverName
from car

??|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...|||dote. had'nt thought about that.|||also, part of the problem is that "license" is spelled wrong ;)|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...

Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.
It's certainly fun trying to write SQL for tables whose columns are called "identifer" and "sirname"|||Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...
What do you expect to happen if your table function returns more than one record or more than one column? And if it always returns one record and one column, then it is a scalar function and should be defined as such.|||Hasn't this something to do with the missing schema name (owner in SQL 2000) when calling the function? Althought it beats me why the @.CarID example seems to work.

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from dbo.getCurrentDriver(car.id)) as driverName
from carsql

Problem inserting XML data

Have SP that uses an xml input variable that the SP iterates over,
inserting the attributes in the xml into the db. This all works fine
until I try to insert embedded xml.
Example:
declare @.in xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50" otherstuff
= "<data><field key="ingredients">aspirin</field></data>"/>'
exec #aspirin_insertSP @.input = @.in
...and here's the create statement on the SP:
CREATE PROCEDURE #aspirin_insertSP
@.input xml
AS
DECLARE
@.Manufacturer varchar(50),
@.Dosage int,
@.otherstuff xml
INSERT INTO Aspirin (Manufacturer, Dosage, otherstuff)
select
Manufacturer = T.c.value('@.Manufacturer', 'varchar(50)'),
Dosage = T.c.value('@.Dosage', 'int'),
thestuff = T.c.value('@.thestuff','varchar(5000)')
from @.input.nodes('/input') T(c)
I've tried all manner of escape characters and CDATA combos to no
avail. Does anyone have any ideas/experience with anything like this?
Surely someone else has run into this before.
Any help would be much appreciated
You need to escape out less-than, greater-than and double quotes
inside the 'otherstuff' attribute (the embedded xml).
Also
thestuff = T.c.value('@.thestuff','varchar(5000)')
should be
otherstuff = T.c.value('@.otherstuff ','varchar(5000)')
|||Also you can get SQL Server to escape the attribute
for you by doing this
declare @.innerXML varchar(1000)
set @.innerXML='<data><field key="ingredients">aspirin</field></data>'
declare @.input xml
set @.input = '<input Manufacturer = "Painkiller1" Dosage = "50" />'
set @.input.modify('
insert attribute otherstuff {sql:variable("@.innerXML")}
into (/input)[1]')
|||Hello rbnbigd@.msn.com,

> Have SP that uses an xml input variable that the SP iterates over,
> inserting the attributes in the xml into the db. This all works fine
> until I try to insert embedded xml.
Correct. Literal XML cannot be embedded within an attribute. That's one of
the XML rules in general. You can have embedded XML within a text node, so
something like this should work:
declare @.in xml,@.in2 xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50"><![CDATA[<data><field
key="ingredients">aspirin</field></data>]]></input>'
insert into Aspirin(Manufacturer,Dosage,otherstuff)
select @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Now if this is XML, why would you want to have it in string format anyway
and not just XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7420bc68c8210fb6695960@.news.microsoft.co m...
> Hello rbnbigd@.msn.com,
>
> Correct. Literal XML cannot be embedded within an attribute. That's one of
> the XML rules in general. You can have embedded XML within a text node, so
> something like this should work:
> declare @.in xml,@.in2 xml
> set @.in = '<input Manufacturer = "Painkiller1" Dosage =
> "50"><![CDATA[<data><field
> key="ingredients">aspirin</field></data>]]></input>'
> insert into Aspirin(Manufacturer,Dosage,otherstuff)
> select
> @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Problem inserting XML data

Have SP that uses an xml input variable that the SP iterates over,
inserting the attributes in the xml into the db. This all works fine
until I try to insert embedded xml.
Example:
declare @.in xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50" otherstuff
= "<data><field key="ingredients">aspirin</field></data>"/>'
exec #aspirin_insertSP @.input = @.in
...and here's the create statement on the SP:
CREATE PROCEDURE #aspirin_insertSP
@.input xml
AS
DECLARE
@.Manufacturer varchar(50),
@.Dosage int,
@.otherstuff xml
INSERT INTO Aspirin (Manufacturer, Dosage, otherstuff)
select
Manufacturer = T.c.value('@.Manufacturer', 'varchar(50)'),
Dosage = T.c.value('@.Dosage', 'int'),
thestuff = T.c.value('@.thestuff','varchar(5000)')
from @.input.nodes('/input') T(c)
I've tried all manner of escape characters and CDATA combos to no
avail. Does anyone have any ideas/experience with anything like this?
Surely someone else has run into this before.
Any help would be much appreciatedYou need to escape out less-than, greater-than and double quotes
inside the 'otherstuff' attribute (the embedded xml).
Also
thestuff = T.c.value('@.thestuff','varchar(5000)')
should be
otherstuff = T.c.value('@.otherstuff ','varchar(5000)')|||Also you can get SQL Server to escape the attribute
for you by doing this
declare @.innerXML varchar(1000)
set @.innerXML='<data><field key="ingredients">aspirin</field></data>'
declare @.input xml
set @.input = '<input Manufacturer = "Painkiller1" Dosage = "50" />'
set @.input.modify('
insert attribute otherstuff {sql:variable("@.innerXML")}
into (/input)[1]')|||Hello rbnbigd@.msn.com,

> Have SP that uses an xml input variable that the SP iterates over,
> inserting the attributes in the xml into the db. This all works fine
> until I try to insert embedded xml.
Correct. Literal XML cannot be embedded within an attribute. That's one of
the XML rules in general. You can have embedded XML within a text node, so
something like this should work:
declare @.in xml,@.in2 xml
set @.in = '<input Manufacturer = "Painkiller1" Dosage = "50"><![CDATA[<data>
<field
key="ingredients">aspirin</field></data>]]></input>'
insert into Aspirin(Manufacturer,Dosage,otherstuff)
select @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/inp
ut/@.Dosage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Now if this is XML, why would you want to have it in string format anyway
and not just XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7420bc68c8210fb6695960@.news.microsoft.com...
> Hello rbnbigd@.msn.com,
>
> Correct. Literal XML cannot be embedded within an attribute. That's one of
> the XML rules in general. You can have embedded XML within a text node, so
> something like this should work:
> declare @.in xml,@.in2 xml
> set @.in = '<input Manufacturer = "Painkiller1" Dosage =
> "50"><![CDATA[<data><field
> key="ingredients">aspirin</field></data>]]></input>'
> insert into Aspirin(Manufacturer,Dosage,otherstuff)
> select
> @.in.value('(/input/@.Manufacturer)[1]','varchar(20)'),@.in.value('(/input/@.D
osage)[1]','int'),@.in.value('(/input/text())[1]','varchar(max)')
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>