how to split a parameter array in stored procedure

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 2005 Forums

Transact-SQL (2005)

how to split a parameter array in stored procedure

Author

Topic

spshindagi
Starting Member

11 Posts

Posted - 2008-01-24 : 01:22:01

i have created an storedprocdure viz SP_TEST; with param @event,@month. both param are of string array. i just wnated to split them and based on the value i wanted to write select query.
eg: @event =injury,opd,icu. @month=jan,feb,mar,apr,dec.
based on this parameter i wanted to retrive respective patient history.
at the first how to split array in stored procdure, and write paricular event.
normally in asp.net c# the following code is excuted as follows.
string[] event = @event .Split('\'', ',');
string[] month = @month.Split('\'', '\"', ',');
public ArrayList mai = new ArrayList();
public string[] janhistory ;
for (int i = 1; i < event.Length; i++)
{
if (event[i].Equals("injury"))
{
foreach (string Items in month)
{
if (Items.Contains("jan"))
{
mai.Add(Items.ToString());
}
}
janhistory = mai.ToArray(Type.GetType("System.String")) as string[];
gh.getmailcomm(janhistory, "Acc398");

love all

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-24 : 02:50:45

see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

spshindagi
Starting Member

11 Posts

Posted - 2008-01-25 : 07:09:08

i have split both array parameter. but how write stored procedure for the following code.
for (int i = 1; i < mvpmnth.Length; i++)
{
if (mvpmnth[i].Equals("1"))//asuume i had written function to split //array parameter
{
foreach (string Items in mvparr)
{
if (Items.Contains("Mail"))//again i have to check condition
{
mai.Add(Items.ToString());
}
}
mailcom = mai.ToArray(Type.GetType("System.String")) as string[];
gh.getmailcomm(mailcom, "Acc398");
}
getmailcomm is function which return values as follows:and finally ihad to retive value from this function.
public void getmailcomm(string[] comm, string acc)
{

k = comm.Length;
getmailcomarr = new string[k];
vasi = new int[k];
vasisid = new int[k];
for (int m = 0; m < k; m++)
{
string l = comm[m];
com = l.Substring(0, 3).ToString();
getma = l.Remove(0, 3).ToString();
com = com + "Comments";

if (getma == "Mail")
{

SqlConnection con = new SqlConnection(cn);
con.Open();
string sql = "Select VAS_Id," + com + ",VAS_Sub_Id from Acc_MVP_Table1 where " + l + "='1' and AccSlno='" + acc + "'";

SqlCommand getmacom = new SqlCommand(sql, con);

rd = getmacom.ExecuteReader();

getmailcomarr[m] = "NoComments";
if (rd.Read())
{

getmailcomarr[m] = rd.GetString(1);
vasi[m] = rd.GetInt32(0);
vasisid[m] = rd.GetInt32(2);

}

}
}

my question is how to check length of parameter? a small code snippet i have tried
ALTER PROCEDURE getMail ( @MailPAram varchar(100),@mnth varchar(100) ) AS
Declare mnthcat nvarchar,
mnthmail nvarchar,
tempstring nvarchar,
@comments nvarchar output,
cnt int,
set tempstring = substring(@mnth(0,3));
set mnthcat = tempstring+"Comments";
set mnthmail=tempstring+"Mail";
BEGIN
cnt =0;

WHILE @mnth.Length LOOP

set @comments = (SELECT Acmvp.VAS_Id, Acmvp.VAS_Sub_Id,#+mnthcat+#
FROM Acc_MVP_Table1 Acmvp
join mvp_func(@MAilPAram) mvp on (mnthmail=mvp.number))
cnt = cnt+1;

love all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 09:00:45

Search within this forums for SPlitData UDF. I guess that is what need here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources