Datatype conversion in Stored Procedures

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)

Datatype conversion in Stored Procedures

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 09:54:25

I am trying to achieve such a command in my stored procedure as shown below. However, the setback is that "itemid" is an integer data type and i wish to store "1,2" data in "@ITEMID" parameter. Tried putting "@ITEMID VARCHAR(100)", but it does not work. Anyone know how i can work around this?

The error message is:
Syntax error converting the varchar value '1,3' to a column of data type int.

[Code]
Select * from m.mstritem
where m.itemid not in (1,2)
[/Code]

[Code]
Stored Procedure:
CREATE PROCEDURE [SEARCHSALEITEM]
(

@ITEMID VARCHAR(100)
)

As
SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND (M.ITEMID NOT IN (@ITEMID))

GO
[/Code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 09:56:51

Try this

SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND @ITEMID not like '%'+M.ITEMID+'%'

Also search for where in @MySQL at this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 10:09:42

Your SQL statement below does not work. The following error is:
Syntax error converting the varchar value '%' to a column of data type int

SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND @ITEMID not like '%'+M.ITEMID+'%'

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 10:23:37

Have a look at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=WHERE+IN+@MyCSV

and the "best split function" link just underneath it

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 10:50:45

Kristen: Sorry, but i dont think the link you provided is what i am looking for. The thing is i wish to take values such as "1,2" and store in as @ITEMID. However, @ITEMID is a int datatype and cannot accept varchar inputs.

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 10:58:56

"@ITEMID is a int datatype and cannot accept varchar inputs"

@ITEMID in your example is varchar(100)

"Split" it into a Temp Table and JOIN that to your table (well, actually, to simulate NOT IN you need a LEFT OUTER JOIN where the joined table PK "IS NULL" to match things that were NOT in the list).

Better than splitting to a temporary table first is to just LEFT OUTER JOIN direct to a function that splits the comma delimited list - for example:


CREATE PROCEDURE [SEARCHSALEITEM]
(

@ITEMID VARCHAR(100)
)

As
SELECT *
FROM MSTRITEM M
    LEFT OUTER JOIN MySplitFunction(@ITEMID)
        ON MySplitValue = M.ITEMID
WHERE (M.ITEMID=I.ITEMID)
    AND MySplitValue IS NULL

Not sure what "I.ITEMID" is though, as there is no alias for "I" in your original example

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 11:04:25

This is the full stored procedure:

CREATE PROCEDURE [SEARCHSALEITEM]
(
@BRAND VARCHAR(100),
@ITEMNAME VARCHAR(50),
@ITEMID VARCHAR(100)
)

As
SELECT M.ITEMID,M.ITEMNAME,M.BRAND,I.ITEMIMGID
FROM MSTRITEM M, ITEMIMAG I
WHERE (M.ITEMID=I.ITEMID) AND (M.ADDTOSALEITEM=1)
AND (M.ITEMID NOT LIKE (@ITEMID))
AND ((M.BRAND LIKE '%' + @BRAND + '%' ) OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
GO

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 11:06:14

Do you have the coding for MySplitFunction(@ITEMID)? Thanks

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 11:14:21

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best+split+functions

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 11:21:40

Pardon me because im quite a newbie in SQL. There are so many split functions to choose from, not sure which to choose? A simple one that works will be fine for me. Any suggestion on whose split function i should use?

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 11:26:12

As you say any one will do. There are debates over which method is fastest, but I agree with you that finding one that is simple and you can understand is certainly good enough to get you going.

A search here for SPLIT and/or CSV should fine you a decent handful!

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 12:00:39

Thanks for your help. I think this link is the simplest and best to use:
[url]http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx[/url]

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 12:17:06

"simplest"

Yup

"best to use"

Nah ... it uses a loop! "Well-slow" as my young daughter would say ...

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 22:01:25

It uses a loop? Does that really affect the performance a lot?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 00:10:26

quote:
Originally posted by shaoen01

Your SQL statement below does not work. The following error is:
Syntax error converting the varchar value '%' to a column of data type int

SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND @ITEMID not like '%'+M.ITEMID+'%'

You need to convert it into varchar

SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND @ITEMID not like '%'+cast(M.ITEMID as varchar(10))+'%'

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-04 : 01:20:16

Thanks it works now. However, i have a few questions to ask. Can you explain to me how does "@ITEMID not like '%'+cast(M.ITEMID as varchar(10))+'%'" works? @ITEMID is a varchar as declared in my stored procedure and may contain information such as "1,2,3" right. But M.ITEMID contains like int data type and will it be able to compare to @ITEMID.

Not sure if you actually understand what i am talking about because i am quite unsure myself. Lol ... Why cant i do it like this?

--My Previous statement--
SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND (M.ITEMID NOT IN (@ITEMID))

--Your statement--
SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND @ITEMID not like '%'+cast(M.ITEMID as varchar(10))+'%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 01:37:49

>>AND (M.ITEMID NOT IN (@ITEMID))

Not in @MyStr is not directly supported and you need to use Dynamic SQL

EXEC('
SELECT *
FROM MSTRITEM M
WHERE (M.ITEMID=I.ITEMID)
AND (M.ITEMID NOT IN ('+@ITEMID+'))'
)

or

the method I suggested

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-04 : 07:09:08

"It uses a loop? Does that really affect the performance a lot?"

In SQL server definitely. SET based methods will usually beat sequential / loop / cursor methods - often by one or more orders of magnitude - that's a key thing to know / learn about SQL.

"AND @ITEMID not like '%'+cast(M.ITEMID as varchar(10))+'%'"

This is too loose - a M.ITEMID value of "1" will match a @ITEMID value of, say, "12,26,31"

What you need for this approach is:


AND ',' + @ITEMID + ','
    NOT LIKE '%,' + CONVERT(varchar(20), M.ITEMID) + ',%'

or the dynamic SQL Madhi suggested.

I still don't know what you want I.ITEMID to represent ??

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-04 : 13:23:56

Do not mind me asking but why did you use a comma inside '%,'? Kind of curious. Anyway, my I.ITEMID is actually refering to another table called ITEMIMAG in my database. It contains the images of items. Previously, i did not copy the full snippet of my stored procedure. Anyway, i did some revision by adding in a "LIKE" clause inside my new stored procedure. But its not working too well.

Your approach:
AND ',' + @ITEMID + ','
NOT LIKE '%,' + CONVERT(varchar(20), M.ITEMID) + ',%'

New Stored Procedure:
EXEC('
SELECT
FROM MSTRITEM M, ITEMIMAG I
WHERE (M.ITEMID=I.ITEMID)
AND (M.ITEMID NOT IN ('+@ITEMID+'))
AND (M.BRAND LIKE %' @BRAND + '%)'
)

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-04 : 13:24:53

I forgot to add in a "+" in the previous post, but still not working.

New Stored Procedure:
EXEC('
SELECT
FROM MSTRITEM M, ITEMIMAG I
WHERE (M.ITEMID=I.ITEMID)
AND (M.ITEMID NOT IN ('+@ITEMID+'))
AND (M.BRAND LIKE %' + @BRAND + '%)'
)

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-04 : 13:27:25

My apologies, i forgot to add in the "*" in my select statement, but still not working.

New Stored Procedure:
EXEC('
SELECT *
FROM MSTRITEM M, ITEMIMAG I
WHERE (M.ITEMID=I.ITEMID)
AND (M.ITEMID NOT IN ('+@ITEMID+'))
AND (M.BRAND LIKE %' + @BRAND + '%)'
)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources