Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: .NET4
Hi friends,
 

Please suggest some ideas to pass the dynamically creating values to Store procedure through SQL parameters?
 
I want to save those values into table where that table is also created dynamically.
 

 

Thanks
 
Siva
Posted 2-Mar-12 18:58pm
Comments
Dave Kreskowiak at 3-Mar-12 0:16am
   
How many columns in this table are we talking about?? What's the upper end of this number??
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

It's not really very easy, since SQL does not have any concept of arrays.
However, you could pass them through as a delimited string, and break it into a temporary table in your procedure:
Pass a list as a string parameter:
DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab
  Permalink  
Comments
SAKryukov at 4-Mar-12 1:05am
   
My 5.
--SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Long back I wrote a procedure that was taking XML string and then using the passed XML to find real data. Perhaps that could do.
 
Also using the XML is not so different from the above solution, so you could use that too. That solution is structuring data using our own format and using XML will also do the same.
  Permalink  
Comments
SAKryukov at 4-Mar-12 1:06am
   
Not clear. How would you parse XML in SQL?
--SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 3 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100