Solved

Loop through Stored Procedure parameters to produce a comma delimited string

Posted on 2006-07-24
11
352 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
Technology Partners: 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!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Querying data from 3 SQL tables 2 45
What's wrong with this T-SQL Foreign Key? 7 59
fault SQL backup files that wont restore - how common 3 57
changing page verifacation 1 62
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…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

733 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