select from table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select from table

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-22 : 12:48:45

Hey,
I need to select values dynamically, i.e, When I enter some values in text box i need to select those related datas from the table ...

tejendi
Starting Member

2 Posts

Posted - 2010-09-22 : 13:06:59

SELECT * FROM [table] WHERE [column name] = [input from your text box]

Something like that you're looking for?

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-22 : 16:46:05

In text box input as like this 1,2,3,4,5,6,7,8

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-22 : 16:48:00

What my need is ..
I have a table contains 100 rows ....
I want to select the particular rows as the need of client ...
If he enters 3,6,9 means that rows must be selected...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-22 : 16:48:37

How to do this...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-22 : 17:43:28

Is this possible?

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 02:44:20

quote:
Originally posted by jafrywilson

Is this possible?

Yes with ROW_NUMBER function

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-23 : 03:00:54

3,6,9 are column values or what ?
What do you mean by related data from the table ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-23 : 05:57:42

Search for Array+SQL Server in google/bing

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 11:55:11

Tnx for response

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 12:08:47

quote:
Originally posted by vaibhavktiwari83

3,6,9 are column values or what ?
What do you mean by related data from the table ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

3,6,9 are rows

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 12:12:25

My table as like this
id mpn details
1 aaa hai
2 bbb how
3 ccc are
4 ddd you
5 . .
6 . .
7 . .
. . .
. . .
. . .
My need is when user enters aaa ,ccc means the details must displayed for him...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-24 : 02:00:51

Either you can use split function and join with the table
Or
You can use dynamic query for the same.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-24 : 02:21:28

Hello,

For example:

DECLARE @param varchar(10)
SET @param = '3,6,9'

SELECT *
FROM <your_table>
WHERE
CharIndex(','+RTrim(Cast(id AS VARCHAR(10)))+',', ','+@param+',')>0

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-24 : 12:39:28

Thanks for reply ...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-24 : 12:40:37

Devart Nice help .. Thank you for your good idea..

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 12:47:39

That's nice and all, but I suspect you will incur a scan everytime



CREATE TABLE myTable99(id int PRIMARY KEY)
GO

INSERT INTO myTable99(id)
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 5 UNION ALL 
SELECT 6 UNION ALL 
SELECT 7 UNION ALL 
SELECT 8 UNION ALL 
SELECT 9
GO

DECLARE @param varchar(10)
SET @param = '3,6,9'

SELECT *
FROM myTable99
WHERE
CharIndex(','+RTrim(Cast(id AS VARCHAR(10)))+',', ','+@param+',')>0
GO
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-24 : 12:58:33

3,6,9 may change ..User can give any value..

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 13:18:31

INDEX SEEK


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Table]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Table]
GO

CREATE FUNCTION dbo.udf_Table(@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(Parameter varchar(255))
AS 

BEGIN
--	SELECT @ParmList = 'a,b,c', @Delim = ','
	DECLARE @x int, @Parameter varchar(255)

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(Parameter) SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+1, LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
	  END 
	INSERT INTO @table(Parameter) SELECT @ParmList
	RETURN
END
GO


CREATE TABLE myTable99(id int PRIMARY KEY)
GO

INSERT INTO myTable99(id)
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 5 UNION ALL 
SELECT 6 UNION ALL 
SELECT 7 UNION ALL 
SELECT 8 UNION ALL 
SELECT 9
GO

DECLARE @param varchar(10)
SET @param = '3,6,9'

	SELECT *
	  FROM myTable99 a
INNER JOIN dbo.udf_Table(@param,',') b
		ON a.id = b.Parameter
GO

DROP TABLE myTable99
GO

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 13:18:45

INDEX SEEK


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Table]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Table]
GO

CREATE FUNCTION dbo.udf_Table(@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(Parameter varchar(255))
AS 

BEGIN
--	SELECT @ParmList = 'a,b,c', @Delim = ','
	DECLARE @x int, @Parameter varchar(255)

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(Parameter) SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+1, LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
	  END 
	INSERT INTO @table(Parameter) SELECT @ParmList
	RETURN
END
GO


CREATE TABLE myTable99(id int PRIMARY KEY)
GO

INSERT INTO myTable99(id)
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 5 UNION ALL 
SELECT 6 UNION ALL 
SELECT 7 UNION ALL 
SELECT 8 UNION ALL 
SELECT 9
GO

DECLARE @param varchar(10)
SET @param = '3,6,9'

	SELECT *
	  FROM myTable99 a
INNER JOIN dbo.udf_Table(@param,',') b
		ON a.id = b.Parameter
GO

DROP TABLE myTable99
GO

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 13:19:03

INDEX SEEK


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_Table]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_Table]
GO

CREATE FUNCTION dbo.udf_Table(@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(Parameter varchar(255))
AS 

BEGIN
--	SELECT @ParmList = 'a,b,c', @Delim = ','
	DECLARE @x int, @Parameter varchar(255)

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(Parameter) SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+1, LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
	  END 
	INSERT INTO @table(Parameter) SELECT @ParmList
	RETURN
END
GO


CREATE TABLE myTable99(id int PRIMARY KEY)
GO

INSERT INTO myTable99(id)
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 5 UNION ALL 
SELECT 6 UNION ALL 
SELECT 7 UNION ALL 
SELECT 8 UNION ALL 
SELECT 9
GO

DECLARE @param varchar(10)
SET @param = '3,6,9'

	SELECT *
	  FROM myTable99 a
INNER JOIN dbo.udf_Table(@param,',') b
		ON a.id = b.Parameter
GO

DROP TABLE myTable99
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources