SP - error converting - newbie question

SP - error converting - newbie question

Post by JH » Sun, 17 Feb 2002 22:48:22



How do I fix this ??

Syntax error converting the varchar value '1, 2, 3, 4, 5' to a column
of data type int.

--
CREATE PROCEDURE my_sp AS



SELECT *
FROM items

/* WHERE itemsIDX IN (1, 2, 3, 4, 5) */
--

--
CREATE TABLE [dbo].[items] (
        [itemsIDX] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
--

 
 
 

SP - error converting - newbie question

Post by Hirantha Hettiarachch » Mon, 18 Feb 2002 02:20:24


JH,
you need dynamic sql for this
try
EXEC ('SELECT *
FROM items

take a look here
http://www.algonet.se/~sommar/dynamic_sql.html
--
hth
Hirantha S. Hettiarachchi MCSD,MCDBA,OCP

Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SP - error converting - newbie question

Post by JH » Mon, 18 Feb 2002 02:31:27


Quote:> you need dynamic sql for this

Thanks for your help.
 
 
 

SP - error converting - newbie question

Post by Erland Sommarsko » Mon, 18 Feb 2002 08:23:40



> JH,
> you need dynamic sql for this
> try
> EXEC ('SELECT *
> FROM items

> take a look here
> http://www.algonet.se/~sommar/dynamic_sql.html

Hirantha, I appreciate that you give a link to my web site. However,
the essence of the article is that you do *not* need dynamic SQL
in this case.

http://www.algonet.se/~sommar/examples_of_lists.html is a direct
entry, and one day when I come around to it, I will rearrange my
web site, so there is a direct entry to it from my home page.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

SP - error converting - newbie question

Post by Hirantha Hettiarachch » Mon, 18 Feb 2002 11:08:23


Erland,
thanks for the correction. I just gave him a refrence for Dynamic sql.  
thanks again.
--
hth
Hirantha S. Hettiarachchi MCSD,MCDBA,OCP

Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SP - error converting - newbie question

Post by Robert Lummer » Mon, 18 Feb 2002 20:26:51


JH,

there's a nice way, too using xml like the following example shows:

**************************

create table tExample(nEx int, bla varchar(128))
go
create proc dbo.spGetExampleByIds

as begin



  select nEx, bla
    from tExample

)
end
go

insert tExample values(1,'brabra')
insert tExample values(2,'hullo')
insert tExample values(3,'oops')
insert tExample values(4,'fart')
go

exec spGetExampleByIds

drop table dbo.tExample
drop proc dbo.spGetExampleByIds

**************************

It's a bit more complex than need be, but as no real array or table
is by now allowed to be input parameter to a stored proc,
this seems to me a good solution, that also allows much more complex
input structures and uses a text encoding, that is supported on all
modern platforms.

good luck

     robert

 
 
 

SP - error converting - newbie question

Post by Bob Barro » Mon, 18 Feb 2002 23:57:09



>How do I fix this ??

>Syntax error converting the varchar value '1, 2, 3, 4, 5' to a column
>of data type int.

>--
>CREATE PROCEDURE my_sp AS



>SELECT *
>FROM items


This is a FAQ.

The In() construct expects a list, either a list of literal values
separated
by commas, or a list of variables, separated by commas. The query
engine
_will not_ parse a variable passed at this point to see if it contains
a
list of values. One reason: the mere presence of commas in a variable
does
not necessarily mean the variable contains a list of values. Don't you
have
any character fields containing data that includes commas in your
database?
Why should the query engine know in one case that you're attempting to
pass
a list of values, and in another, you're passing the actual data
(commas and
all) that you want it to find in the column? Remember, at compile
time, the
query engine has no idea what that variable will contain. It _can't _
be
expected to know that it has to parse the future contents of the
variable
just because it's the only variable appearing in the In() construct.

There are two ways to accomplish what you're after:

1) use charindex to search for the values:

    Of course, this will force a table scan, but performance may be
adequate for your needs. You will also need to code delimiters into
the variable and the statement, to keep '4' from being found in this
list: '3','34','44'.

You .can use any delimiter you want

2) dynamic SQL:





every time
you run it, but the resulting impact on performance is not likely to
be as
great in SQL7 as it was 6.5. Again - you will need to test both
solutions to
see which one better suits your needs.

Here's a third way (only works with SS2000 and above) - thanks to

- Show quoted text -

>  you could use xml, too:

>   create table tblExample(intPK int)
>   insert tblExample values(3)
>   insert tblExample values(56)
>   insert tblExample values(34)
>   insert tblExample values(300)
>   insert tblExample values(301)
>   go

>   create proc [tmp] as begin



>     select * from tblExample
>     where intPK in(
>       select v from openxml(

>       )
>         with(v int)
>     )


>   end
>   go

>   exec tmp
>   go

>   drop procedure tmp
>   drop table tblExample
>   go

For SQL 2000, you can use a UDF (thanks to Marc Litchfield:

... it's
pretty easy to write a UDF to parse a comma-delimited string and
return a
table variable (this is called a table-valued function), so you
could do something like this:

    SELECT  a.IdentityID
    FROM  tblExample ex

Which would return the position of the value of ex.intPK within the

udfSplitInt:

[---- Begin SQL ----]
-- Function:    udfSplitInt
-- Description: Returns a table variable from a string containing a
--                  delimited list of integers
-- Author:      Marc Litchfield, 09/05/01
--
CREATE FUNCTION udfSplitInt
(


)

        IdentityID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
        Element int NULL )
AS
BEGIN









BEGIN




END,



0

END,',','')




END

RETURN
END
[---- End SQL ----]

Thanks to Michael Walsh, here's yet another way:


AccountID  +  ',%' )


note that there is no space after the comas.

It works simply. If AccountID  is  45,  clearly    ',1,4,5,7,'  LIKE
'%,45,%'    returns false.
    If AccountID  is  4, on the other hand,   ',1,4,5,7,' LIKE
'%,4,%'
returns true.

So, you have, in effect, an IN( )  where the list is a parameter.

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.

 
 
 

SP - error converting - newbie question

Post by JH » Tue, 19 Feb 2002 05:33:16


Quote:> There are two ways to accomplish what you're after:

Thanks for your explanation, very instructive. I used the second
solution, after the answer from Hirantha.
 
 
 

SP - error converting - newbie question

Post by JH » Tue, 19 Feb 2002 05:33:12


Quote:> It's a bit more complex than need

I see that I still have a lot to learn ;)
 
 
 

1. how to convert this sp from oracle to sql server 7/2000 compatible (newbie)

Greetings.
I have a function in Oracle that I need to convert to ms sql server
7/2000 (must run on both).

function MyFunctionEnabled(OracleError in out integer,
                          ErrMsg1 in out varchar2,
                          ErrMsg2 in out varchar2) return boolean

I am not so sure about the return value being boolean, or even the
concept of a function in ms sql server 7.

Do I need to do this (following), or can someone suggest/show me a
better way:

CREATE    PROCEDURE MyFunctionEnabled




Thanks
Jeff

2. cursor type

3. Newbie question reg. Views/SP

4. VB forms to Access forms converter?

5. Newbie questions on SP

6. Filemaker on OSX (10.2) and MySQL via ODBC

7. Newbie Question... updating via SP

8. What wrong with my SQL Statement?

9. SP newbie question

10. Newbie SP question

11. Newbie Oracle sp question

12. Newbie question: Converting access to SQL

13. newbie question how to convert Excel formula