September 1, 2010 at 5:28 pm
Ok I swear this will be my last question for a while. I have a query that is generated inside a stored proc with which I pass parameters. Is there anyway to query based on a set of parameters? I have a front end web application that has a web form where the user uses a list and can pick several items from it. I'm not too sure what the output of this is at the moment my guess would be a comma delimited string, generated form the drop down using VB code. I need to somehow pass this into my stored proc so it can return the result set and generate a report.
The situation would be something like the process below (simplified to get to the point so I'm not going to use a stored Proc in this example)
-- Test data
DECLARE @MyTable TABLE
(
CityID INT,
CityName nvarchar(50)
)
INSERT INTO @MyTable (CityID,CityName)
SELECT 1,'Toronto' UNION ALL
SELECT 2,'Winnipeg' UNION ALL
SELECT 3,'Vancouver' UNION ALL
SELECT 4,'Ottawa' UNION ALL
SELECT 5,'Calgary' UNION ALL
SELECT 6,'Edmonton' UNION ALL
SELECT 7,'Charlotte Town'
-- String to be passed to query from web page
DECLARE @FrontEndString nvarchar(255)
SELECT @FrontEndString = '1,3,4'
-- Actual query to get results
SELECT
CityID,
CityName
FROM
@MyTable MT
WHERE
CityID IN(@FrontEndString)
Does this warrant the use of Dynamic SQL somehow? I don't like the idea of dynamic SQL mainly because I have yet to use it, but if there is not other way, perhaps I will try reading up on that. Also, I must give the user a option to select all values.
Previously I have been using something like the following, where the drop down list to select the city is bound to the table and so returns the correct ID, and I have manually entered another item to the drop down list with ID = -1 called 'All'. A good assumption is that the ID will never be negative so I am justified in using -1.
WHERE
(CityID = @CityID OR @CityID = -1)
Just want to make sure there is no other way to do this first. p.s. I know the above query does not work 🙂
September 1, 2010 at 5:35 pm
In general you'd usually create a function that would break your comma delimited string down into a table for usage. For a local SQL Server Central example: http://www.sqlservercentral.com/scripts/Miscellaneous/31913/
From there, you'd join the lookup table to the resulting table with something along these lines (pseudocode)
SELECT
city
FROM
CityLookup AS cl
JOIN
fn_StringToTable(@FrontEndString) AS st
ON cl.id = st.col1
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 2, 2010 at 11:27 am
Is this the only work around? I was hoping for something more straight forward, maybe I'm just expecting too much. This kinda complicates the "All" option as well as it would not make the join.
September 2, 2010 at 11:46 am
loki1049 (9/2/2010)
Is this the only work around? I was hoping for something more straight forward, maybe I'm just expecting too much. This kinda complicates the "All" option as well as it would not make the join.
It's not the only way, just probably the best way. For what is (IMHO) the best string splitting function, click here for the latest Delimited Split Function.
An alternative is dynamic sql:
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT
CityID,
CityName
FROM
@MyTable MT
WHERE
CityID IN(' + @FrontEndString + ')';
EXECUTE (@SQL);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 11:52 am
You can use charindex too, but convert the string into table is better performance in general.
EX: select * from @temp where charindex(id,@str) > 0
Note, don't use this. It is a really bad performance
WHERE
(CityID = @CityID OR @CityID = -1)
September 2, 2010 at 12:25 pm
Thanks Wayne, I will look into that post. I don't want to go into dynamic SQL as I don't think it should really be used. I'm not a fan of having code be continally re-written each time the SP is executed. I like concrete things, they tend to sit better with me for some reason.
And "TheCoding", I know I've seen lots of articles that say this is a bad idea, but I need some default way to select all of a certain ID in the case that one is not given. If you have some other means of doing this let me know.
September 2, 2010 at 12:55 pm
loki1049 (9/2/2010)
Thanks Wayne, I will look into that post. I don't want to go into dynamic SQL as I don't think it should really be used. I'm not a fan of having code be continally re-written each time the SP is executed. I like concrete things, they tend to sit better with me for some reason.And "TheCoding", I know I've seen lots of articles that say this is a bad idea, but I need some default way to select all of a certain ID in the case that one is not given. If you have some other means of doing this let me know.
CREATE PROCEDURE dbo.GetAllProc AS
SELECT
CityID,
CityName
FROM
@MyTable MT
GO
CREATE PROCEDURE dbo.GetSomeFromDelimStringProc (@FrontEndString varchar(7999)) AS
SELECT
MT.CityID,
MT.CityName
FROM
@MyTable MT
JOIN dbo.DelimitedSplit8K(@FrontEndString, ',') ds
ON MT.CityID = ds.Item
GO
CREATE PROCEDURE dbo.MainProc (@FrontEndString varchar(7999) = NULL) AS
IF @FrontEndString IS NOT NULL
exec dbo.GetAllProc
ELSE
exec dbo.GetSomeFromDelimStringProc @FrontEndString
GO
The reason for doing it like this is so that each procedure has it's own, non-changing execution plan. This will be better in the long run for you.
Again, click here for the latest Delimited Split Function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 1:28 pm
Wayne, thanks for that example, and yes I am now using that splitter function to create a table to join to. That works well, and I am happy with it, but this whole idea of splitting up the SP into separate cases of querying seems like it might be a PITA for me. You see, in lots of cases, the user wants a choice to filter a view by several different criteria, some of which can be select all or just select one. That is why I have used that parameterized where clause. Below is a typical version of how I would do this:
SELECT
Field1ID,
Field1,
Field2ID,
Field2,
Field3,
Field4
FROM
MyView JOIN Othertables
ON MyView.asdf = Othertables.asdf
WHERE
(DateField BETWEEN @date1 and @date2)
AND
(Field1ID = @field1ID OR -1 = @field1ID)
AND
(Field2ID = @field2ID OR -1 = @field2ID)
Something like that, so you say this is bad because of the OR parts or that you should typically not use parameters in the where clause? If I did what you propose wayne, I would have to make 3 different SP for this case to hand all possible out comes, and there might be another SP where I use this -1 logic three times, so thats a whole lotta SP's and makes things quite ugly.
Thanks for the help so far though!
September 2, 2010 at 7:37 pm
And for what is perhaps the best explanation of the issues this causes, see Gail Shaw's blog[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 3, 2010 at 10:03 am
loki1049 (9/2/2010)
Wayne, thanks for that example, and yes I am now using that splitter function to create a table to join to. That works well, and I am happy with it, but this whole idea of splitting up the SP into separate cases of querying seems like it might be a PITA for me. You see, in lots of cases, the user wants a choice to filter a view by several different criteria, some of which can be select all or just select one. That is why I have used that parameterized where clause. Below is a typical version of how I would do this:
SELECT
Field1ID,
Field1,
Field2ID,
Field2,
Field3,
Field4
FROM
MyView JOIN Othertables
ON MyView.asdf = Othertables.asdf
WHERE
(DateField BETWEEN @date1 and @date2)
AND
(Field1ID = @field1ID OR -1 = @field1ID)
AND
(Field2ID = @field2ID OR -1 = @field2ID)
Something like that, so you say this is bad because of the OR parts or that you should typically not use parameters in the where clause? If I did what you propose wayne, I would have to make 3 different SP for this case to hand all possible out comes, and there might be another SP where I use this -1 logic three times, so thats a whole lotta SP's and makes things quite ugly.
Thanks for the help so far though!
If you are concerned AT ALL about performance you simply cannot to what you have above.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 3, 2010 at 11:47 am
loki1049 (9/2/2010)
I don't want to go into dynamic SQL as I don't think it should really be used. I'm not a fan of having code be continally re-written each time the SP is executed. I like concrete things, they tend to sit better with me for some reason.
No offense, but it is too bad you have this bias against dynamic sql. It really is a very powerful tool in SQL Server to utilize.
However, it does get a bad reputation based on people using it incorrectly. But being informed of this you can certainly read up on the measures to take against these things. There is quite a bit of useful info. on this forum about the proper way to go about using dynamic sql. Also an often cited link detailing a great deal is http://www.sommarskog.se/dynamic_sql.html
September 3, 2010 at 1:07 pm
Thanks for all the info guys, and ya, I may have just read too many bad things about dynamic SQL to ever trust it ha. As for the selecting all option, I have decided to just the idea orignally stated, i.e. generating a comma delimited string, using the Parsing function wayne suggested and creating a table to join to. In the case that I want to do a select all type of operation, I will simply give the user an option of all in the list box, then in the code behind when I go to generate the comma delimited string I will just test for the case that All has been selected as an option and load all values from the listbox to be sent as a string and then generated into a table. That way I can just join to that function in the base query, and worry about sending only 1 parameter. I hope this fixes the performance issue you all figure I will run into.
On a side note, I have been using the WHERE (columnid = @parameter OR -1=@parameter) logic inside a stored proc to query a quite complex view made of several cross joins, derived tables, and a union. When I pass the parameters it takes about 1min 40sec to run. When I try to just query the view by itself i.e. without the (columnid = @parameter OR -1=@parameter), it tends to run even slower. I'm not too sure why...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply