C
Chuck P
I have a web page where a user can select multiple items for use in a select
statement. SELECT columns FROM table WHERE ID in (1,2,3). Since its a web
application I use a stored procedure to get the results.
Currently my sproc GetResults (@EventIdList varChar(@256))
uses a table value function that parses the item list and returns a table.
I use this method because I don't do dynamic SQL in sprocs and sprocs can't
take arrays.
Select columns from MyTable
INNER JOIN dbo.ParseIntToTable(@EventIdList,',') evn on MyTable.EventTypeID
= evn.ColumnValue
ALTER FUNCTION [dbo].[ParseIntToTable]
(
@StringList varchar(8000),
@Delimiter char(1)
)
RETURNS
@TempTable table
(
ColumnValue int
)
AS
BEGIN
DECLARE @StringValue varchar(1000) -- value taken off list
DECLARE @Pos int -- position of delimiter
SET @StringList = LTRIM(RTRIM(@StringList))
IF RIGHT(@StringList,1) <> @Delimiter
SET @StringList = @StringList + @Delimiter
SET @Pos = CHARINDEX(@Delimiter, @StringList, 1)
IF REPLACE(@StringList, @Delimiter, '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @StringValue = LTRIM(RTRIM(LEFT(@StringList, @Pos - 1)))
IF @StringValue <> ''
BEGIN
INSERT INTO @TempTable (ColumnValue)
VALUES (CAST(@StringValue AS int))
END
SET @StringList = RIGHT(@StringList, LEN(@StringList) - @Pos)
SET @Pos = CHARINDEX(@Delimiter, @StringList, 1)
END
END
RETURN
In LINQ how do you write a query that takes an array for a parameter. (
select columns from table where ID IN (1,2,3) ).
The query result will be used in a GridView and needs to support paging and
sorting.
thanks,
statement. SELECT columns FROM table WHERE ID in (1,2,3). Since its a web
application I use a stored procedure to get the results.
Currently my sproc GetResults (@EventIdList varChar(@256))
uses a table value function that parses the item list and returns a table.
I use this method because I don't do dynamic SQL in sprocs and sprocs can't
take arrays.
Select columns from MyTable
INNER JOIN dbo.ParseIntToTable(@EventIdList,',') evn on MyTable.EventTypeID
= evn.ColumnValue
ALTER FUNCTION [dbo].[ParseIntToTable]
(
@StringList varchar(8000),
@Delimiter char(1)
)
RETURNS
@TempTable table
(
ColumnValue int
)
AS
BEGIN
DECLARE @StringValue varchar(1000) -- value taken off list
DECLARE @Pos int -- position of delimiter
SET @StringList = LTRIM(RTRIM(@StringList))
IF RIGHT(@StringList,1) <> @Delimiter
SET @StringList = @StringList + @Delimiter
SET @Pos = CHARINDEX(@Delimiter, @StringList, 1)
IF REPLACE(@StringList, @Delimiter, '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @StringValue = LTRIM(RTRIM(LEFT(@StringList, @Pos - 1)))
IF @StringValue <> ''
BEGIN
INSERT INTO @TempTable (ColumnValue)
VALUES (CAST(@StringValue AS int))
END
SET @StringList = RIGHT(@StringList, LEN(@StringList) - @Pos)
SET @Pos = CHARINDEX(@Delimiter, @StringList, 1)
END
END
RETURN
In LINQ how do you write a query that takes an array for a parameter. (
select columns from table where ID IN (1,2,3) ).
The query result will be used in a GridView and needs to support paging and
sorting.
thanks,