quote:

---

Originally posted by sross81

Hello,

I created a stored procedure that can go one of two ways depending on what the user passes in for the description parameter. If the user passes in * then it will just pull all descriptions. But if the user does not pass in * they have the option to choose just one or multiple descriptions. I tried to set it up in my where clause and I wanted to know if I set it up right. It compiles successfully, but when I try to run it through SSMS to test it I am not able to because I am not sure how to pass in multiple descriptions without it thinking I am passing in two many.....here is how I tried to pass it in

exec get_chart_rpt_electronicflag
'07/01/2008','07/16/2008','description1' 'description2'


Below is the code I used to create the SP:

alter procedure get_chart_rpt_electronicflag
(@begdate as datetime, @enddate as datetime,@description varchar(75)) as


if @description = '*'
SELECT cast( f.med_rec_nbr as int) as MedRec#,a.first_name, a.last_name,
cast( b.appt_date as datetime) as ApptDate,
convert(datetime,stuff(b.begintime,3,0,':')) as begintime,
b.cancel_ind, c.location_name, d.description,e.e_chart
FROM person a
JOIN appointments b ON a.person_id = b.person_id
JOIN provider_mstr d ON b.rendering_provider_id = d.provider_id
JOIN location_mstr c ON b.location_id = c.location_id
left outer join patient_ e on a. person_id = e.person_id
join patient f on a.person_id = f.person_id
where b.appt_date >= @begdate and b.appt_date <=@enddate

if @description <> '*'
SELECT cast( f.med_rec_nbr as int) as MedRec#,a.first_name, a.last_name,
cast( b.appt_date as datetime) as ApptDate,
convert(datetime,stuff(b.begintime,3,0,':')) as begintime,
b.cancel_ind, c.location_name, d.description,e.e_chart
FROM person a
JOIN appointments b ON a.person_id = b.person_id
JOIN provider_mstr d ON b.rendering_provider_id = d.provider_id
JOIN location_mstr c ON b.location_id = c.location_id
left outer join patient_ e on a. person_id = e.person_id
join patient f on a.person_id = f.person_id
where b.appt_date >= @begdate and b.appt_date <=@enddate
and d.description = @description + ' '


Thanks in Advance!
Sherri

---

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

quote:

---

Originally posted by sross81

Thank you. I am researching that now and trying to make sense of it :)

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

Thanks in Advance!
Sherri

---

quote:

---

Originally posted by tkizer

Not CSV tables, CSV functions. Your app needs to pass the multiple values as a comma separated list, or similar delimiter. Once they are CSV'ed, you use a function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

quote:

---

Originally posted by sross81

I have read about csv tables. I guess I am just unsure on how to get my values into a csv table. It looks like in all the examples they are selecting a list of values from a table and inserting that into a parameter. I want to take a list of values that a user chooses. For example in the form they can select
description 1
description 2
description 3
and I want all 3 of those to pass into the @description parameter so it would be like select * from table where description IN (@description), but I know that is not the correct syntax....wouldn't that be nice! :)

How can I take the list of values that they select in so I can put it in a csv?

quote:

---

Originally posted by sross81

Thank you. I am researching that now and trying to make sense of it :)

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

Thanks in Advance!
Sherri

---

Thanks in Advance!
Sherri

---

SELECT fields
FROM yourtable
WHERE ',' + @Descriptionlist ',' LIKE '%,'+ yourdescriptionfield + ',%'

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sross81

I have read about csv tables. I guess I am just unsure on how to get my values into a csv table. It looks like in all the examples they are selecting a list of values from a table and inserting that into a parameter. I want to take a list of values that a user chooses. For example in the form they can select
description 1
description 2
description 3
and I want all 3 of those to pass into the @description parameter so it would be like select * from table where description IN (@description), but I know that is not the correct syntax....wouldn't that be nice! :)

How can I take the list of values that they select in so I can put it in a csv?

quote:

---

Originally posted by sross81

Thank you. I am researching that now and trying to make sense of it :)

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

Thanks in Advance!
Sherri

---

Thanks in Advance!
Sherri

---

You will pass them as comma seperated values from the application. then you will be having a single parameter in sp to receive them.let it be @Descriptionlist varchar(max). then your select will be

SELECT fields
FROM yourtable
WHERE ',' + @Descriptionlist ',' LIKE '%,'+ yourdescriptionfield + ',%'

---

quote:

---

Originally posted by sross81

Ok this is making more sense....but how do I put my values into the variable that is passed to the function. Here is the csv function example I found on this site. I don't see exactly where the values are passed into it to be formed into a list. I see how it creates an array and separates by commas but I don't see where the values actually come in....


Posted - 07/29/2008 : 18:49:18
--------------------------------------------------------------------------------

Ok so I need to use a function.....I still am not sure where a function goes. Does it go in a stored procedure? I found this one but am not sure how to tweak it. What pieces in here actually need to be changed and what are actually part of what makes it work? I know my data type is not int instead its varchar. I don't see in the function how it gets the list of values? I assume that it has to somehow reference the list of values the user passes in because it eventually references the function in a select statement that references a parameter but it doesn't say where to reference the function. I can't find anything that gives me a good starting point.


source: http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

select P.ProductID, ProductName
from dbo.Products P
where p.ProductID in (Select IntValue
from dbo.CsvToInt(@TheCSV) )




Create Function dbo.CsvToInt ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sross81

I have read about csv tables. I guess I am just unsure on how to get my values into a csv table. It looks like in all the examples they are selecting a list of values from a table and inserting that into a parameter. I want to take a list of values that a user chooses. For example in the form they can select
description 1
description 2
description 3
and I want all 3 of those to pass into the @description parameter so it would be like select * from table where description IN (@description), but I know that is not the correct syntax....wouldn't that be nice! :)

How can I take the list of values that they select in so I can put it in a csv?

quote:

---

Originally posted by sross81

Thank you. I am researching that now and trying to make sense of it :)

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

Thanks in Advance!
Sherri

---

Thanks in Advance!
Sherri

---

You will pass them as comma seperated values from the application. then you will be having a single parameter in sp to receive them.let it be @Descriptionlist varchar(max). then your select will be

SELECT fields
FROM yourtable
WHERE ',' + @Descriptionlist ',' LIKE '%,'+ yourdescriptionfield + ',%'

---

Thanks in Advance!
Sherri

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sross81

Ok this is making more sense....but how do I put my values into the variable that is passed to the function. Here is the csv function example I found on this site. I don't see exactly where the values are passed into it to be formed into a list. I see how it creates an array and separates by commas but I don't see where the values actually come in....


Posted - 07/29/2008 : 18:49:18
--------------------------------------------------------------------------------

Ok so I need to use a function.....I still am not sure where a function goes. Does it go in a stored procedure? I found this one but am not sure how to tweak it. What pieces in here actually need to be changed and what are actually part of what makes it work? I know my data type is not int instead its varchar. I don't see in the function how it gets the list of values? I assume that it has to somehow reference the list of values the user passes in because it eventually references the function in a select statement that references a parameter but it doesn't say where to reference the function. I can't find anything that gives me a good starting point.


source: http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

select P.ProductID, ProductName
from dbo.Products P
where p.ProductID in (Select IntValue
from dbo.CsvToInt(@TheCSV) )




Create Function dbo.CsvToInt ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sross81

I have read about csv tables. I guess I am just unsure on how to get my values into a csv table. It looks like in all the examples they are selecting a list of values from a table and inserting that into a parameter. I want to take a list of values that a user chooses. For example in the form they can select
description 1
description 2
description 3
and I want all 3 of those to pass into the @description parameter so it would be like select * from table where description IN (@description), but I know that is not the correct syntax....wouldn't that be nice! :)

How can I take the list of values that they select in so I can put it in a csv?

quote:

---

Originally posted by sross81

Thank you. I am researching that now and trying to make sense of it :)

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

Thanks in Advance!
Sherri

---

Thanks in Advance!
Sherri

---

You will pass them as comma seperated values from the application. then you will be having a single parameter in sp to receive them.let it be @Descriptionlist varchar(max). then your select will be

SELECT fields
FROM yourtable
WHERE ',' + @Descriptionlist ',' LIKE '%,'+ yourdescriptionfield + ',%'

---

Thanks in Advance!
Sherri

---

how are you getting values from user? what's your front end?

---

quote:

---

Originally posted by sross81

Oh I see what you mean and I see why I am confused too. I am using Crystal Reports 11 as a front end. I don't think I can pass a comma separated list from crystal. I can do it from within crystal if I am not using a stored procedure and just link directly to the table I need to query but if I link to a stored procedure it disables the option to use multiple values. The crystal report just gets dropped into a bigger application where I have no control over the back end at all besides to add reports. I can see how i could do that from a .Net app but where I currently work unfortunately I don't have that kind of flexibility. Maybe its just not possibly with the interfaces I have available.

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sross81

Ok this is making more sense....but how do I put my values into the variable that is passed to the function. Here is the csv function example I found on this site. I don't see exactly where the values are passed into it to be formed into a list. I see how it creates an array and separates by commas but I don't see where the values actually come in....


Posted - 07/29/2008 : 18:49:18
--------------------------------------------------------------------------------

Ok so I need to use a function.....I still am not sure where a function goes. Does it go in a stored procedure? I found this one but am not sure how to tweak it. What pieces in here actually need to be changed and what are actually part of what makes it work? I know my data type is not int instead its varchar. I don't see in the function how it gets the list of values? I assume that it has to somehow reference the list of values the user passes in because it eventually references the function in a select statement that references a parameter but it doesn't say where to reference the function. I can't find anything that gives me a good starting point.


source: http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

select P.ProductID, ProductName
from dbo.Products P
where p.ProductID in (Select IntValue
from dbo.CsvToInt(@TheCSV) )




Create Function dbo.CsvToInt ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by sross81

I have read about csv tables. I guess I am just unsure on how to get my values into a csv table. It looks like in all the examples they are selecting a list of values from a table and inserting that into a parameter. I want to take a list of values that a user chooses. For example in the form they can select
description 1
description 2
description 3
and I want all 3 of those to pass into the @description parameter so it would be like select * from table where description IN (@description), but I know that is not the correct syntax....wouldn't that be nice! :)

How can I take the list of values that they select in so I can put it in a csv?

quote:

---

Originally posted by sross81

Thank you. I am researching that now and trying to make sense of it :)

quote:

---

Originally posted by tkizer

You can't do this in the way that you think you can. You need to pass in multiple values to a single parameter and then your code needs to handle this. People typically handle this with a CSV function. Search here for the various articles and threads that cover this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

Thanks in Advance!
Sherri

---

Thanks in Advance!
Sherri

---

You will pass them as comma seperated values from the application. then you will be having a single parameter in sp to receive them.let it be @Descriptionlist varchar(max). then your select will be

SELECT fields
FROM yourtable
WHERE ',' + @Descriptionlist ',' LIKE '%,'+ yourdescriptionfield + ',%'

---

Thanks in Advance!
Sherri

---

how are you getting values from user? what's your front end?

---

Thanks in Advance!
Sherri

---