Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query help 1

Status
Not open for further replies.

rds80

Programmer
Nov 2, 2006
124
US
This is the code:

Code:
DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '''Z'', ''Y'''
PRINT @ControlIDList

SELECt *
FROM TestTable
WHERE ControlID (' + @ControlIDList + ')

What I want to do is Select all the fields where ControlID in Z and Y. But no results come back.
 
Are there 3 tables, 'z', 'y' and 'test table' or just 1 or 2? Please explain more.




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
1 Table - TestTable
I want to select all the fields where controlid is in 'Z' and 'Y'. Sorry I forgot the IN

Code:
DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '''Z'', ''Y'''
PRINT @ControlIDList

SELECT *
FROM TestTable
WHERE ControlID IN (' + @ControlIDList + ')
 
Code:
Select *
From TestTable
Where ControlID = 'Z' OR ControlID = 'Y'

No need for variables unless you are using a stored procedure.




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
I'll be using a udf...just simplifying the logic for now
@ControlIDList is what I'll be passing into the udf
 
Code:
Select *
From TestTable
Where ControlID = @ControlIDList

Or if it is part of a string (i saw the 'in' before)

Code:
Select *
From TestTable
Where ControlID = '%' + @ControlIDList + '%'




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Code:
RETURNS nvarchar(4000)
AS
BEGIN
	DECLARE @MyOutput varchar(4000)
	SET @MyOutput = ControlID
        Where ControlID = 'Y' or COntrolID = 'Z'
	RETURN @MyOutput
END




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
oops, add

Code:
CREATE FUNCTION dbo.ControlID(@MyOutput nvarchar(4000))
to the top before returns




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Sorry doesn't seem that my question is clear..

SELECT @ControlIDList = '''Z'', ''Y'''

will not only be Z and Y. What I'll eventually have is @ControlIDList as a parameter to the function. So the value for @ControlIDList can change and for that reason I cannot have Where ControlID = 'Y' or COntrolID = 'Z'

I tried
Code:
DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '''Z'', ''Y'''
PRINT @ControlIDList

SELECT *
FROM TestTable
WHERE ControlID IN (SELECT @ControlIDList)

But the result from the query is empty.

Any suggestions?
 
Try...

Code:
DECLARE @ControlIDList nvarchar(4000)
SELECT @ControlIDList = '[!],Z,Y,[/!]'
PRINT @ControlIDList

SELECT *
FROM TestTable
WHERE [!]@ControlIdList Like '%,' + ControlID + ',%'[/!]

Of course, this is assuming that the ControlId column is a string type (char, nchar, varchar, nvarchar). If it's an integer column, then you'll need to convert to varchar first.

You'll need to add commas to the @ControlIdList before the first piece of data and after the last piece of data.

Here is a working example that you can copy/paste to Query Analyzer to see how it works.

Code:
Declare @Temp Table(ControlID VarChar(20))

Insert Into @Temp Values('x')
Insert Into @Temp Values('y')
Insert Into @Temp Values('z')


DECLARE @ControlIDList varchar(4000)
SELECT @ControlIDList = ',Z,Y,'
Select @ControlIdList

SELECT *
FROM @TEmp
WHERE @ControlIdList Like '%,' + ControlID + ',%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Thanks Michael and George..
George - your last post is exactly what I was looking for.
Michael - I should've tried your query out.

Is this what the WHERE clause is doing:
WHERE ,Z,Y, LIKE %,A,B,C,D,Z,Y,%'
and then it selects the values WHERE it is just Z,Y?

How did you guys know this??
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top