Solved

Loop through Stored Procedure parameters to produce a comma delimited string

Posted on 2006-07-24
11
305 Views
Last Modified: 2008-03-10
Hello All,
  I am using a stored procedure to facilitate auditing (after my last post I was dissuaded from using the Log Explorer software for auditing). It look something like this:-

...
I run the first stored procedure then I run the Audit Trail stored procedure
...

Dim StoredProc as string
StoredProc = "<<stored procedure name>>"
Dim Parameters as string
Dim cmd As SqlCommand = New SqlCommand(StoredProc, objConnection)
cmd .CommandType = CommandType.StoredProcedure
cmd .Parameters.Add("@UserId", Session("UserId"))
cmd .Parameters.Add("@Parameters", ???????????)

My question is how can I loop through the parameters in the first stored procedure to give me a string? (e.g. Test1, Test2, Test3...) I am writing the contents of this into one table no matter what stored proc is run.

Any help would be appreciated!

mcdermon
0
Comment
Question by:mcdermon
  • 6
  • 5
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17167442
please see this previous thread:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21627393.html

I posted there a function "ParmsToList" that can be used to generate a recordset from a comma-delimited string.

This should help to implement what you need.
0
 

Author Comment

by:mcdermon
ID: 17167597
I had a look at your function but to be honest I am not sure what I am supposed to do with it! Could you give me some more help?

Thanks for your answer!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17167626
Well, unless you post more information what you want to have done resp problems with it will be difficult to be clearer.

anyhow:

select * from dbo.ParmsToList('1,2,3,4')

should give you a output like this:

column
1
2
3
4

Hopefully that clarifies how to use that function in your procedure...

0
 

Author Comment

by:mcdermon
ID: 17167871
I am sorry, what I really meant to say is that I am trying to GET the comma delimited string, I don't already have it.  I would like to perform a loop to add each of these

cmd.Parameters.Add("@UserId", Session("UserId"))

to one long string. I know that you can count the parameters added, but geting the value is my real problem. I could actually be in the wrong section - maybe wed dev would have been more appropriate.

My apologies.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17168129
>I could actually be in the wrong section - maybe wed dev would have been more appropriate.
post a pointer question over there (20 points)

>I would like to perform a loop to add each of these
well, you should NOT dynamically create the parameters collection, but do that manually.
I know, alot of coding efforts, but avoids problems
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:mcdermon
ID: 17168429
I know that you have advised against it but srtictly speaking I am not dynamically creating the parameters - just a list of their values for audit purposes. I think this nearly works...

For i = 1 To objCommand.Parameters.Count
    Params = Params & objCommand.Parameters(i).Value & ","
Next i

...but it falls over when a date is encountered. Any more thoughts?

p.s. are the pointer questions over there <<< or over there >>>. I could be just being dim, but I can't see it!

Thanks.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17168518

For i = 1 To objCommand.Parameters.Count
    Params = Params & "'" &  objCommand.Parameters(i).Value & "',"
Next i
0
 

Author Comment

by:mcdermon
ID: 17168563
I have tried that but get the following message:-

Cast from type 'SqlDateTime' to type 'String' is not valid

Am very perplexed!
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17168576

For i = 1 To objCommand.Parameters.Count
    Params = Params & "'" &  objCommand.Parameters(i).Value.ToString() & "',"
Next i
0
 

Author Comment

by:mcdermon
ID: 17168949
Thanks sooooo much for your help. It works now and I have saved myself a HUGE headache. It's just a case of apply to all Stored Procs, but just one last small question seeing as you have given me such great advice so far - if you were told to audit an sql db would you do an audit trail of the select statements?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17169556
>if you were told to audit an sql db would you do an audit trail of the select statements?
only of those tables that are of interest for auditing select statements, and also only partially (not the selects of everybody)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now