August 23, 2004 at 9:39 pm
I need to pass a set of data to a single
parameter of the Sproc.
Ex.
spStock @Company,@Year
For the Company, I need to pass more than
one company from the front end and use the company parameter in
the IN Statement.
Select * from Stock Where Company IN ('A','B') and Year = '2004'
If I pass I get the sproc as
spStock ''A'',''B'','2004'
else
spStock 'A,B','2004'
How to use this.
August 24, 2004 at 5:43 am
SQL Server will not allow you to say
Select * from Table where ColumnName in (@Paramater)
Options include:
1) Use dyanmic SQL:
declare @DynSQL varchar(1000)
declare @Company varchar(1000) --Parameter declaration would be in proc declaration
set @Company = "A, B" --Parameter would be sent in this format
set @DynSQL = "Select * from Stock where Company in (" + @Company + ")"
print @DynSQL
exec(@DynSQL)
Disadvantage is that the user has to have rights to execute whatever is in the @DynSQL statement in addition to rights to execute the stored procedure.
2) You may be able to send the companies in as XML and parse them using SQLXML. I'm not quite as familiar with this concept.
3) Send the companies in as a comma-delimited string and then parse them into a temporary table of just ids. You could then join Stocks to your temp table in your query.
August 24, 2004 at 12:08 pm
Not terribly scalable, but you could use a temporary table to do this. For example:
CREATE TABLE #params (ParamValue varchar(5) not null)
INSERT INTO #params('A')
INSERT INTO #params('B')
EXEC spStock '2004'
DROP TABLE #params
Then, in your stored procedure,
SELECT s.*
FROM Stock s
INNER JOIN #params p ON s.Name = p.ParamValue
WHERE Year = @YearParam
Since temporary tables with single #'s are connection specific, multiple connections could all be running the same query concurrently w/o contention or clashes.
-- Mitch
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply