January 9, 2004 at 9:39 am
I read an article earlier on how to send a list to a stored procedure, I need info on this as soon as possible.
How do I parse this parameterized list if it is delimited.
thanks
January 9, 2004 at 9:56 am
I didn't read the article, so I'm not sure that what you mean by parameterized list is what I think of when I hear parameterized list.
A parameterized list to me means a list of parameters. When you create a stored procedure, you can add parameters to it. Like so:
Create Procedure dbo.MyProcedure
@Parameter1 int,
@parameter2 varchar(50),
@Parameter3 sysname,
@Parameter4 datetime = getdate()
AS
Then when you call the sp, you can pass in the list of parameters in a couple of easy ways. Either include the list of parameters in the exact same order as they appear in the sp or include the list of parameters in no particular order by naming them to match the parameters in the sp.
So in Query Analyzer, here are two examples of how to call the sp:
1. Include the list of parameters in the exact same order as they appear in the sp:
Exec dbo.MyProcedure 1, 'SecondParam', 'MyTable', '7/4/1776'
2. Include the list of parameters in no particular order by naming them to match the parameters in the sp:
Exec dbo.MyProcedure @Parameter1 = 1, @Parameter3 = 'MyTable', @Parameter4 = '7/4/1776', @parameter2 = 'SecondParam'
There is no parsing to do. Each parameter is assigned a variable name (@Parameter1, and so on) that you use in the sp to refer to each parameter. Like so:
Create Procedure dbo.MyProcedure
@Parameter1 int,
@parameter2 varchar(50),
@Parameter3 sysname,
@Parameter4 datetime = getdate()
AS
Declare @SQL varchar(2000)
Set @SQL = 'Update [' + @Parameter3 + '] Set Field2 = ''' + @parameter2 + ''' Where FieldID = ' + @Parameter1 + ' And FieldUpdateDate > ''' + @Parameter4 + ''''
Exec(@SQL)
Go
January 9, 2004 at 10:59 am
It could be that what reddyk needs is the ability to receive a comma-delimited string of arguments and parse out the individual arguments, or something similar. If so, the following code might be enough to get you started (pretend that the whole snippet of code is really a procedure definition, and that @myString is really an input parameter). I did this in a hurry, without referring to any existing code templates, so make sure I'm not suffering from off-by-one-itis or any other similar problems before adopting this approach. The important thing is that you can use charindex:
declare @myString varchar(80) -- paramater to be parsed
set @myString = 'arg1,arg2,arg3,arg4'
declare @delimiter char(1) -- delimiter within @myString
set @delimiter = ','
declare @start int -- starting point of current arg in @myString
set @start = 0
declare @currentDelimiter int -- position of current comma (or end-of-string) in @myString
set @currentDelimiter = 0
declare @arg varchar(80) -- current arg
while @currentDelimiter <= len(@myString)
begin
set @start = @currentDelimiter + 1
set @currentDelimiter= charindex (@delimiter, @myString, @start)
if @currentDelimiter = 0
set @currentDelimiter = len(@myString) + 1
set @arg = substring (@myString, @start, @currentDelimiter - @start)
print @arg
end
Good luck!
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply