sql queries again - 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

sql queries again

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-29 : 01:22:18

i hav this problem .i hav one column in my table which contains records which can be integers or string.
i allow user in my .aspx page to enter integers in three text boxes.
and depending on those integers say 1,2,3 ... i hav to select those rows from my table.
where the problem is that i hav to selct the row which is say row no.1 which can contain string or integer and iam not getting the slected result instead some integers .
the query iam using is
SELECT " & range1 & " UserID FROM TAble

where range1 is the integer that user puts into textbox .i hav only one cloumn(UserID) in my table which can contain integer or string so i hav to select the row which is what the user has entered but contains string or integer(which is not what user has entered).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 01:33:08

Create StoredProcedure which accepts CSV as input and do query based on that

Create Procedure Search_sp
(@myCSV varchar(2000))
as
Select columns from yourTable where @myCSV like '%'+cast(UserId as integer)+'%'

Call this sp from Front End application

Also search for Where @mySQL here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-29 : 01:36:04

post the table data, input and the desired output..

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-29 : 01:51:21

table contains

UserID
a
b
c
d
e
i input 1,2,3 into my frontend thereby selecting a,b,c from table.how do i do that.the query iam using
SELECT " & range1 & " UserID FROM Table
SELECT " & range2 & " UserID FROM Table
SELECT " & range2 & " UserID FROM table
Where range1-3 are inputs that i put in frontend.

So iwant a,b,c to be selected as i inputed 1,2,3 in frontend

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-29 : 02:12:16

select top 1 userid from (select top " & range1 & " UserID FROM Table ) a order by userid desc
you can substitute range1 with 1 or 2 or 3 subsequently

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 02:14:02

How do you link 1,2,3 to a,b,c?
Does your table have other column that has this information?

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-29 : 02:34:52

now i want to insert those selected into another table but iam getting interger 1,2,3, instead of a,b,c query used by me
INSERT Table2(UserID) SELECT TOP " & range1 & " UserID FROM Table ORDER BY UserID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 03:39:32

How do you link 1,2,3 to a,b,c?
Did you read the question I asked you?

Read this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-29 : 03:49:54

no i haven't linked them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 04:02:21

If you input 11,12,13 then what do you expect the query to return?

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-29 : 07:03:23

i expect query to return the rows at 11,12,13.
thats what the problem is iam supposed to use only one cloumn .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 07:43:42

You need to have other column, probably identity, and match with it and get data

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-30 : 00:53:15

ok now i have a identity column how do i proceed ,and one more question how do i reset the identity column in table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-30 : 00:58:04

Select Name from yourTable where @myCSV like '%' +cast(id as int) +'%'

To reset the identity column, you need to truncate the table that deletes all data and reset identity if any

If you want to keep the data

DBCC CHECKIDENT (TableName, RESEED, 0)

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2005-12-30 : 04:24:11

i got that problem solved thank u.
now i want to reset the identity column in my table along with other column as i hav to use that table again.qury used by me is
TRUNCATE TABLE table1.Identitycolumn.Column1
i tried running this query in sql query but it says that it does not support TRUNCAT STatement

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-30 : 05:03:04

>>TRUNCATE TABLE table1.Identitycolumn.Column1

That should be

TRUNCATE TABLE table1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources