sql stored procedure using IN Statement

Question

hi below is my stored procedure, but im not able to get the results

alter PROCEDURE homepageitems

        @categoryid int,
                @websiteid  int
        ,@websiteitems VARCHAR(15)


AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   SELECT Product.id, Product.name, Product.Price, categoryid FROM product where  Product.Published=1 and Product.Deleted=0
     and Categoryid=@categoryid and websiteid=@websiteid and product.id in (' + @websiteitems + ')
END


GO

--------------

DECLARE    @return_value int

EXEC    @return_value = [dbo].[homepageitems]
        @categoryid = 67,
        @websiteid = 2,
        @websiteitems = N'75530,75667,75518,75953'

SELECT    'Return Value' = @return_value

GO

Msg 245, Level 16, State 1, Procedure homepageitems, Line 20
Conversion failed when converting the varchar value ' + @websiteitems + ' to data type int.

and Im supplying the correct values of websiteitems (75530,75667,75518,75953) and Product.id is integer field, how do i supply when im using IN Statement

Answer 1

These articles on my web site explains why your approach is not working, and describes (too) many methods to achieve what you are looking for. Read the article that fits your SQL Server version. Thankfully, you do not need to read it all:

http://www.sommarskog.se/arrays-in-sql.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

 CREATE FUNCTION [dbo].[SplitString]
     (
         @str VARCHAR(MAX)
     )
    RETURNS @ret TABLE (token VARCHAR(MAX))
     AS
     BEGIN
     
    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, ',', '</t><t>') + '</t>'
    
    INSERT INTO @ret
        SELECT x.i.value('.', 'VARCHAR(MAX)') AS token
        FROM @x.nodes('//t') x(i)
    RETURN
   END

-----Usage
CREATE TABLE #t (c INT)
INSERT INTO #t VALUES (1),(5),(3)

DECLARE @array VARCHAR(50)='1,5'
SELECT * FROM #t 
WHERE c IN (SELECT * FROM SplitString(@array))

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

This flat out does not work, as you have found.  As someone else said, you can use a method to split the string, but I find the best way to do this is to use XML.  You can find a variety of articles at http://technet.microsoft.com/en-us/library/ms190936(v=sql.90).aspx.  Basically, XML is the easiest, most trustworthy way to pass in an arbitrary number of values, turn them in to a table, and do operations using that table ( for example, joining to it for a select ).

---

Christian Graus

Answer 4

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you have no idea). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. 

This is not a correct schema design; can you fix this mess? 

A procedure is named <verb>_<object>.
There is no such thing as a “category_id” in RDBMS! A column is “<attribute>_<attribute property>” but this is two <attribute property>'s without an <attribute>. This is like adjectives without a noun! 

Using integers to encode the homepage categories is an awful design. Have you been to a library? Look at the Dewey Decimal Classification system. Noobs never bother to properly design encoding schemes; really hopeless noobs use IDENTITY! 

Aren't websites named and not numbered? What integer did you use to get to this website? See the point? 

There is no such thing as a generic “id” or “name”  or "category" in RDBMS. Table names are collective or plural nouns to show us that they are sets. 

We do not use flags in RDBMS, so your “publication_flag” and “deletion_flag” are both verbs (attributes are noun!) and a huge design flaw. 

Finally, you have made one of the classic Noob errors that most people un-learn in 3rd or 4th week of SQL. You cannot past a string to IN() and expect that string to be parsed for you! Think about how silly that is and get a laugh at yourself. SQL is compiled, not interpreted like 1960's BASIC! 

Google my articles on the long parameter list for the procedure. But your real problem is an awful schema, improperly designed in both the DDL and data architecture. 

Post the DDL and maybe we can fix it. But you need to get a book on basic data modeling as well as one on SQL. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL