March 4, 2012 at 8:35 am
Hi,
If(@username='Ram')
Begin
Select Professor,Stu_name,Location,Subject From student where Location in('Hyderabad','Banglore','Chennai')
End
Else if(@username='Krishna')
Begin
Select Professor,Stu_name,Location,Subject From student where Location in('Pune','Mumbai','Kolkata')
End
Ii don't need to to Hard code like above..
I want logic like , if user logined with ram..i need to set Location,if user logined with krishna ..i need to set Location...
by using SET we can set only one value at a time for a variable...so i need to set multiple values for Location variable when user logined with that particular username..how can i do it...
@Declare @location varchar(20)
If(@username='Ram')
Begin
set @location='Hyderabad','Banglore','Chennai'
end
Else if(@username='krishna')
Begin
set @location='Chennai','Pune','Mumbai'
End
Select Professor,Stu_name,Location,Subject From student where Location=@location
So i want logic like above ....
Please Help me
March 4, 2012 at 9:13 am
You could use an auxiliary table with username and Location (having three rows for user 'Ram' and three separate rows for user 'Krishna'.
Then query this table using the CROSS APPLY approach.
March 5, 2012 at 8:17 am
Create a function to split a string into a table:
GO
CREATE FUNCTION [dbo].[SplitString] (@list nvarchar(MAX), @separator nvarchar(MAX) = ';')
RETURNS @table TABLE (VALUE nvarchar(MAX))
AS
BEGIN
DECLARE @position int
DECLARE @previous int
SET @list = @list + @separator
SET @previous = 1
SET @position = CharIndex(@separator, @list)
WHILE @position > 0 BEGIN
IF @position - @previous > 0
INSERT INTO @table
VALUES (Cast(SubString(@list, @previous, @position - @previous) AS nvarchar(Max)))
IF @position >= Len(@list) BREAK
SET @previous = @position + 1
SET @position = CharIndex(@separator, @list, @previous)
END
RETURN
END
GO
Then you can treat a string like a table:
DECLARE @location varchar(MAX)
SET @location='Hyderabad,Banglore,Chennai'
SELECT * FROM [dbo].[SplitString](@location,',')
So your select would look like:
SELECT Professor,Stu_name,Location,Subject
FROM Student
WHERE Location IN(SELECT Value Location
FROM [dbo].[SplitString](@location,','))
March 5, 2012 at 8:48 am
LutzM (3/5/2012)
If you decide to go for the "split string solution", at least use the "fast lane"[/url] and not the "pedestrian walk" 😉
+1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 8:53 am
suresh0534 (3/4/2012)
by using SET we can set only one value at a time for a variable...so i need to set multiple values for Location variable when user logined with that particular username..how can i do it...
By using SET, you are trying to force a set-based operation into a procedural operation. Whenever you have multiple values for something, you are dealing with a set of some kind. Most of the time, if you have a set, you want to keep it as a set instead of trying to compress into into a single value.
Given that you have a table that lists user locations--if you don't have one, you should think about creating one--the query would be something like the following.
SELECT Professor, Stu_Name, Location, Subject
FROM Student
WHERE Location IN (
SELECT Location
FROM UserLocations
WHERE [Username] = @Username
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 5, 2012 at 8:55 am
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:
The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 5, 2012 at 9:11 am
You don't need to set a variable @location for thing you are want to do.
Create a UserLocation table which will hold all locations per user:
-- just an example, you want to design this tabel better, having PK, and proper FK
-- to User table etc.
CREATE TABLE dbo.UserLocation
( LocationId INT NOT NULL IDENTITY(1,1)
,Username varchar(50)
,Location varchar(50)
)
INSERT dbo.UserLocation VALUES ('Ram','Hyderabad')
INSERT dbo.UserLocation VALUES ('Ram','Banglore')
INSERT dbo.UserLocation VALUES ('Ram','Chennai')
INSERT dbo.UserLocation VALUES ('Krishna','Pune')
INSERT dbo.UserLocation VALUES ('Krishna','Mumbai')
INSERT dbo.UserLocation VALUES ('Krishna','Chennai')
-- then you query can just join to it without any SET and variable...
Select s.Professor,s.Stu_name,s.Location,s.Subject
From student as s
JOIN UserLocation as ul
ON ul.Location = sites.Location
where ul.Username=@username
March 5, 2012 at 9:30 am
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
The solution I gave allowed him store the values in a string and also satisfied his request: "So i want logic like above "
March 5, 2012 at 9:34 am
Jonathan AC Roberts (3/5/2012)
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
The solution I gave allowed him store the values in a string and also satisfied his request: "So i want logic like above "
That is true. But you really should read the link Lutz posted. It is the same solution but the performance is astronomically better than a loop. It would be better for both the OP and you to abandon the looping construct for splitting strings and use the set based approach detailed in that article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 9:38 am
Sean Lange (3/5/2012)
Jonathan AC Roberts (3/5/2012)
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
The solution I gave allowed him store the values in a string and also satisfied his request: "So i want logic like above "
That is true. But you really should read the link Lutz posted. It is the same solution but the performance is astronomically better than a loop. It would be better for both the OP and you to abandon the looping construct for splitting strings and use the set based approach detailed in that article.
Yes I have read it but it's horses for courses. That bit of code is less easy to understand than the function I gave and the difference it would make in this example is negligible. If you think it will make the query performance astronomically better you need your head testing.
March 5, 2012 at 9:43 am
Jonathan AC Roberts (3/5/2012)
Sean Lange (3/5/2012)
Jonathan AC Roberts (3/5/2012)
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
The solution I gave allowed him store the values in a string and also satisfied his request: "So i want logic like above "
That is true. But you really should read the link Lutz posted. It is the same solution but the performance is astronomically better than a loop. It would be better for both the OP and you to abandon the looping construct for splitting strings and use the set based approach detailed in that article.
Yes I have read it but it's horses for course, that bid of code is less easy to understand than the function I gave and the difference it would make in this example is negligible. If you think it will make the query performance astronomically better you need your head testing.
No need to get snarky.
Here is the reality of using loop based splitters. Yes in this case it will be fine for performance. But at some point in the future the need to split a string will come up again. Of course being an efficient and smart programmer they will use this same loop based splitter again. This time it will be on a 10,000 row table and they will not understand why the performance if hideous. Now if the OP uses a set based approach this time, the next time they will benefit from not having to unlearn a loop based approach.
Now if you could kindly step down from your high horse we could get back to the topic at hand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 9:47 am
Jonathan AC Roberts (3/5/2012)
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
The solution I gave allowed him store the values in a string and also satisfied his request: "So i want logic like above "
But you did not say how to get from the multiple values to the string. It doesn't do the OP much good to assume that he already has a string when he's specifically asking how to create the string.
Furthermore, it's a disservice to encourage the OP to take an approach that is clearly inefficient. Why spend cycles converting from a set to a string and then back to the original set when you can just cut out the whole process and simply start with the original set?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 5, 2012 at 11:03 am
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
drew.allen (3/5/2012)
Jonathan AC Roberts (3/5/2012)
Create a function to split a string into a table:The original question was about taking multiple values and storing them in a single variable, not doing the reverse.
Drew
The solution I gave allowed him store the values in a string and also satisfied his request: "So i want logic like above "
But you did not say how to get from the multiple values to the string. It doesn't do the OP much good to assume that he already has a string when he's specifically asking how to create the string.
Furthermore, it's a disservice to encourage the OP to take an approach that is clearly inefficient. Why spend cycles converting from a set to a string and then back to the original set when you can just cut out the whole process and simply start with the original set?
Drew
Yes, I agree that hard coding locations inside code is a bad idea when the data should be stored in a table. In which case he needs to insert the data into a table and just query the table as you pointed to in your first message.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply