Help: How to select multivalues for a param in SP

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

General SQL Server Forums

New to SQL Server Programming

Help: How to select multivalues for a param in SP

Author

Topic

Nav522
Starting Member

27 Posts

Posted - 2010-03-19 : 09:01:31

Hello FOlks,
I am having a stored procedure which takes 3 parameters. This SP is used to populate a crystal report.And when i try to choose the multivalue options its not populating the data. Please throw some light on how to define multivalue parameters.Thanks a lot
Alter Procedure [dbo].[sp_CMS_rptCollectionRecoveries_BO] (

@ClientID numeric(18,0),
@Frequency varchar(20)) ( need the Frequency to take multivalue here)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-19 : 09:09:27

You can try comma seperated value for the variable frequency

Vaibhav T

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-19 : 09:10:51

what do you mean by "multivalue" ? CSV ?

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql-2005.html

KH
[spoiler]Time is always against us[/spoiler]

Nav522
Starting Member

27 Posts

Posted - 2010-03-19 : 11:23:49

Hey Thanks for getting back.am using the stored procedure for populating a crystal report.And when i run the report i was unable to enter multiple values for the parameter frequency. Wondering whether i might have to change the declaration or something. Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:30:25

You need pass them as csv and then use either of solutions provided in links to filter your data based on individual values in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nav522
Starting Member

27 Posts

Posted - 2010-03-19 : 11:37:07

How to pass them as csv?

Alter Procedure [dbo].[sp_CMS_rptCollectionRecoveries_BO] (
@ClientID numeric(18,0),
@Frequency =('MONTHLY','YEARLY') varchar(20))

Is that correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:46:33

Nope

EXEC [dbo].[sp_CMS_rptCollectionRecoveries_BO] cilentidvalue, 'MONTHLY,YEARLY,..'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nav522
Starting Member

27 Posts

Posted - 2010-03-19 : 11:58:43

I will try that out Thanks and how can we get the date for the previous 18 months? i.e if we are selecting now the date should pull from September 1st 2008 until February 28th 2010. Thanks for getting back.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:09:44

[code]select dateadd(mm,datediff(mm,0,getdate())-18,0) AS StartDate,dateadd(mm,datediff(mm,0,getdate()),0)-1 AS EndDate[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nav522
Starting Member

27 Posts

Posted - 2010-03-19 : 12:31:09

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:31:52

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources