Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Loop through Stored Procedure parameters to produce a comma delimited string

Posted on 2006-07-24
11
Medium Priority
?
387 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 143

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 143

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 143

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
 

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

804 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