Dynamic SQL Query (variable entries, and variable entries per column)
Sorry for the long post, but most of it is code spelling out my scenario:
I'm trying to execute a dynamic query (hopefully through a stored proceedure) to retrieve results based on a variable number of inputs.
If I had a table:
(dbo).(People)
ID Name Age
1 Joe 28
2 Bob 32
3 Alan 26
4 Joe 27
I want to allow the user to search by any of the three columns, no problem:
DECLARE @ID int, @Name nvarchar(25), @Age int
SET @ID = 1
SET @Name = 'Joe'
SET @Age = null
SELECT *
FROM dbo.People
WHERE
(ID = @ID or @ID is null) AND
(Name like @Name or @Name is null) AND
(Age = @Age or @Age is null)
And I retrieve the result that I want.
Now, if I want to search for multiple fields in a column, I can do that no problem:
DECLARE @text nvarchar(100)
SET @text = '1, 3'
DECLARE @ids AS TABLE (n int NOT NULL PRIMARY KEY)
--//parse the string into a table
DECLARE @TempString nvarchar(300), @Pos int
SET @text = LTRIM(RTRIM(@text))+ ','
SET @Pos = CHARINDEX(',', @text, 1)
IF REPLACE(@text, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @TempString = LTRIM(RTRIM(LEFT(@text, @Pos - 1)))
IF @TempString <> '' --just: IF @TempString != ''
BEGIN
INSERT IN开发者_开发知识库TO @ids VALUES (@TempString)
END
SET @text = RIGHT(@text, LEN(@text) - @Pos)
SET @Pos = CHARINDEX(',', @text, 1)
END
END
SELECT *
FROM dbo.People
WHERE
ID IN (SELECT n FROM @ids)
Now, my issue is I can't seem to figure out how to combine the two since I can't put:
WHERE
(Name like @Name or @Name is null) AND
(Id IN (SELECT n FROM @ids) or @ids is null)
Because @ids will never be null (since it's a table)
Any help would be greatly appreciated!
Thanks in advance...and let me know if I can clarify anything
You could use an IF statement:
IF LEN(@ids) > 0
BEGIN
SELECT *
FROM dbo.People
WHERE ID IN (SELECT n FROM @ids)
END
ELSE
BEGIN
SELECT *
FROM dbo.People
END
Otherwise, consider making the query real dynamic SQL (minding pitfalls of course).
Try:
(Id IN (SELECT n FROM @ids) OR NOT EXISTS (SELECT * FROM @ids))
You can try:
NOT EXISTS (SELECT 1 FROM @ids)
OR EXISTS (SELECT 1 FROM @ids where n = Id)
But these better be small tables - this query will probably not play very well with any indexes on your tables.
A quick fix:
(`%,' + Id + ',%' like ',' + @ids + ',' or @ids is null)
and (`%,' + Name + ',%' like ',' + @names + ',' or @names is null)
So if the user passes @ids = 1,2
, the first row gives:
`%,1,%' like ',1,2,'
It's a good idea to filter out spaces before and after comma's. :)
精彩评论