I need help...!

Hello!
I am using IN to determine if a given StateId values matches any value in a
query.
Previously, I used following query:
SELECT * FROM Cities WHERE StateId IN (1, 2, 3)
but now I would like to pass dynamic values as a input parameter...
SELECT * FROM Cities WHERE StateId IN(@.StateId)
StateId is an integer.
Could anyone help me with this, I can't get it working. I am new to SQL
Server.
Thank you!
JamesJames
CREATE PROCEDURE mysp
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC mysp
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"James T." <gimenei@.hotmail.com> wrote in message
news:O7P%23D8dOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am using IN to determine if a given StateId values matches any value in
a
> query.
> Previously, I used following query:
> SELECT * FROM Cities WHERE StateId IN (1, 2, 3)
> but now I would like to pass dynamic values as a input parameter...
> SELECT * FROM Cities WHERE StateId IN(@.StateId)
> StateId is an integer.
> Could anyone help me with this, I can't get it working. I am new to SQL
> Server.
> Thank you!
> James
>|||The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"James T." wrote:

> Hello!
> I am using IN to determine if a given StateId values matches any value in
a
> query.
> Previously, I used following query:
> SELECT * FROM Cities WHERE StateId IN (1, 2, 3)
> but now I would like to pass dynamic values as a input parameter...
> SELECT * FROM Cities WHERE StateId IN(@.StateId)
> StateId is an integer.
> Could anyone help me with this, I can't get it working. I am new to SQL
> Server.
> Thank you!
> James
>
>

I need help with a Query and Count

I have a pretty simple query:

Select title, avg(score) from votes group by title order by avg(score) Desc

This returns a result like this:

Abbey Road 4.0
The White Album 3.5
Meet the Beatles 3.0

The values in the score field are always 1, 2, 3, 4, or 5

What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this.

Abbey Road 4.0 0 0 1 1 1
The White Album 3.5 0 1 0 0 1
Meet the Beatles 3.0 2 2 2 2 2

I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets.

Any suggestions would be greatly appreciated,

Chris

I found a solution using sum and case.

Chris