Solved

Loop through Stored Procedure parameters to produce a comma delimited string

Posted on 2006-07-24
11
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

705 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