Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Loop through Stored Procedure parameters to produce a comma delimited string

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
mcdermon
Asked:
mcdermon
  • 6
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mcdermonAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mcdermonAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
mcdermonAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

For i = 1 To objCommand.Parameters.Count
    Params = Params & "'" &  objCommand.Parameters(i).Value & "',"
Next i
0
 
mcdermonAuthor Commented:
I have tried that but get the following message:-

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

Am very perplexed!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

For i = 1 To objCommand.Parameters.Count
    Params = Params & "'" &  objCommand.Parameters(i).Value.ToString() & "',"
Next i
0
 
mcdermonAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now