July 15, 2010 at 3:34 pm
I have a stored procedure that needs to be changed to accept one or more values.
These values will then be used to decide two things:
Which data rows to process based on a column value equal to a parameter value.
Which order these need to be processed in.
The sproc currently accepts no parameters because we only had one type of dataset to process when it was created.
The only other catch is that the sproc is run manually and I don't see a way to load values into a table-value parameter using that execute method.
Any suggestions?
July 15, 2010 at 5:44 pm
Could you post the procedure as you see it ... i.e. something like
IF (@Parm <> 0)
BEGIN
...
END
ELSE
BEGIN
.....
END
As far as running manually, you could consider passing in a comma delimited string and within the procedure parse that string into each individual value. IF you elect this method I would strongly recommend reading this SSC article by Jeff Moden
http://www.sqlservercentral.com/articles/T-SQL/63003/
Although I must say you appear to be wanting to produce a very complex procedure and might wish to re-think your approach.
July 16, 2010 at 9:15 am
In answer to your last question, how to pass the table-valued parameter when executing the procedure, you can create a table variable of the same type, put the data in it, and then pass that into your procedure. e.g.
DECLARE @values PARAMTYPE;
INSERT INTO @values ...
EXEC yourProc @tblParam = @values
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
July 16, 2010 at 10:24 am
Yeah, I may hove to create a function or something to do the insert for the TVP. I think the person who runs this does a simple EXEC commanad on the simpler, no parameter version they run now.
Was hoping someone had a brilliant and simple trick to do that. Will probably have to do it longhand with an IF.
Thanks!
July 16, 2010 at 10:44 am
Sorry, miscommunicated what the parameters are for. We receive a file from our client and use SSIS to pull it into an intermediate table to process. The file will contain two "sets" of data that need to be separated and the order in which they need to be processed may change at random depending upon circumstances.
Was trying to come up with an easy way to let that be designated without using a parameter list. I was searching for a purely set-based approach to the parameter issue. A TVP seemed like a great way to do it, but can't load the TVP values unless I create a function or sproc as an intermediate step.
I work with some surprisingly brilliant people that do things with T-SQL that makes me scratch my head and wish I was that creative. But they haven't come up with a simple solution to this either. So I will have to go with a slightly procedural approach.
Thanks for the input.
July 16, 2010 at 12:30 pm
Thanks for the help. Based on your input and the need for user interaction at the beginning I came up with this:
Created a table type for the parameters:
CREATE TYPE paramTbl AS TABLE ( value nvarchar(50)NULL, pOrder int NULL )
Created a new sproc to take the parameter values individually. Takes up to 6 parameters and assumes the order entered is the order of processing. Deletes any blank parameter values from the table type.
CREATE PROCEDURE CallProcessCorpCampaignImports
-- Add the parameters for the stored procedure here
@Param1 nvarchar(20),
@Param2 nvarchar(20),
@Param3 nvarchar(20),
@Param4 nvarchar(20),
@Param5 nvarchar(20),
@Param6 nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ParmTbl dbo.paramTbl
--load the table with the parameter values
INSERT INTO @ParmTbl (value, pOrder)
VALUES (@Param1, 1),
(@Param2, 2),
(@Param3, 3),
(@Param4, 4),
(@Param5, 5),
(@Param6, 6)
--Cleanup any parameters with NULL values
DELETE from @ParmTbl WHERE value IS NULL
-- Insert statements for procedure here
EXEC ProcessCorpCampaignImports @ParmTbl
END
GO
Then added this join to the query in the receiving sproc to make sure that we process all sets requested in the order they are entered. Note that they do not necessarily need to be in separate processes, simply in a specified order.
INNER JOIN @ParmTbl p ON p.value = cd.value
ORDER BY p.pOrder
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply